--- title: "pk80005_assignment stored procedure documentation" summary: "Analysis of Oracle package pk80005_assignment, the attorney-assignment batch engine for legal hearing scheduling, conflict checking, and Outlook/calendar integration." status: draft owner: SAIF Documentation Generator last_reviewed: 2026-08-31 source_schema: "WCIS_DBA" source_name: "pk80005_assignment" source_type: "PACKAGE" retrieval_status: "found" retrieval_command: "legends oracle query --owner SAIF --name pk80005_assignment" retrieval_date: "2026-08-31" --- # pk80005_assignment `WCIS_DBA.pk80005_assignment` is the Oracle PL/SQL package that implements SAIF's automated attorney-assignment batch process for workers' compensation legal hearings. It selects unassigned hearings, applies a chain of conflict-of-interest and workload rules to candidate attorneys, records assignment outcomes, and exposes read-only cursors consumed by the .NET assignment-process, attorney-conflict, and Outlook-integration screens. ## Overview | Field | Value | | --- | --- | | **Schema** | WCIS_DBA | | **Type** | Package | | **Database** | WCIS_DBA / Oracle | | **Calling libraries** | `SAIF.Business.Claim.Legal` | | **Calling classes** | `clsAssignmentProcess`, `clsAttorneyConflict`, `clsAttorneySchedule` | | **Member count** | 42 | > The prior (not-found) version of this page listed 12 members, including a duplicate `CalendarReview`/`calendar_review` pair and a `run_assignment` member. The retrieved package spec contains only `calendar_review` (no differently-cased duplicate) and does not declare any `run_assignment` member; that name is dropped here as it does not correspond to a real declared member.* All 41 other real package members are documented below, including 30 not previously listed (mostly internal conflict-check functions, workload calculators, and the case-selection/assignment procedures called as part of the batch cycle). ## Purpose The package automates assignment of workers' compensation hearing cases to attorneys (and, for out-of-town rotation, to outside counsel) as part of SAIF's Legal case-management batch cycle. A top-level driver invokes an ordered sequence of `assign_*` procedures — by employer/policy relationship, DCBS cases, reconsideration cases, out-of-town rotation, same-time/same-location grouping, health-issue and worker-conflict cases, critical claims, Portland-specific cases, and finally any remaining cases — each of which cursors over `PENDA`-status legal cases with unassigned `SET`/`RESET` hearings and calls the shared `assignIt`/`assignItBalanced` functions to evaluate a candidate attorney against conflict-of-interest checks (employer, adjuster, ALJ, attorney, rotation, calendar/vacation, and location conflicts) and caseload thresholds. Every evaluation is recorded via `insert_results` with a result code (`CA`/`CAC` for a successful assignment, or a `NA*` code identifying the blocking reason), and `updateSchedule` persists the winning attorney back onto the `schedule` row. The package also exposes read-only cursors (`get_results`, `get_assign_results`, `get_assign_list`, `calendar_review`, `getOutlookFields`) used by the assignment-review UI and Outlook calendar/attorney-conflict integration screens. ## Procedures / Methods ### adjuster_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (conflict count) | | **Called by** | `assignIt`, `assignItBalanced` (internal package callers) | **Description:** Counts active (`exp_date IS NULL`) `attorney_conflict` rows linking the candidate attorney to the claim's assigned adjuster, matching the adjuster's `user_id` on the claim to the conflict's `adjuster_emplid`. A non-zero count blocks assignment with result code `NAADJ`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_case_seq` | IN | NUMBER | — | `legal_case_seq_num` of the case being evaluated | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | ### employer_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (conflict count) | | **Called by** | `assignIt`, `assignItBalanced` | **Description:** Counts active `attorney_conflict` rows for the candidate attorney where the conflict's `policy_num` matches the claim's policy. A non-zero count blocks assignment with result code `NAEMP`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_case_seq` | IN | NUMBER | — | `legal_case_seq_num` of the case being evaluated | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | ### attorney_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (conflict count) | | **Called by** | `assignIt`, `assignItBalanced` | **Description:** Counts active `attorney_conflict` rows where the candidate's `atty_provider_location_seq_num` matches the claimant's worker's-attorney firm location on the case, i.e. the candidate already represents the opposing/worker side. A non-zero count blocks assignment with result code `NAATT`. This check is unconditional (not gated by a flag) in `assignIt`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_case_seq` | IN | NUMBER | — | `legal_case_seq_num` of the case being evaluated | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | ### alj_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (conflict count) | | **Called by** | `assignIt`, `assignItBalanced` | **Description:** Counts active `attorney_conflict` rows where `conflict_legal_person_seq_num` matches the hearing's assigned ALJ. A non-zero count blocks assignment with result code `NAALJ` when `alj_flag = 'Y'`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_alj_seq` | IN | NUMBER | — | `alj_person_seq_num` of the hearing's ALJ | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | ### calendar_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (conflict count) | | **Called by** | `assignIt`, `assignItBalanced` | **Description:** Counts overlapping `schedule` rows (status `SET`/`RESET`) for the candidate attorney whose date range overlaps `in_date` (spanning multi-day durations via `days_num`) and whose ALJ or hearing location differs from the target hearing — i.e. the attorney is already double-booked elsewhere at the same time. For `AM`/`PM` half-day durations the overlap check is restricted to same-duration or multi-day rows; otherwise it checks any overlapping row. Triggered when `calendar_flag` is `Y`, `H`, or `C`; result code `NAC`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | | `in_date` | IN | DATE | — | Hearing date being evaluated | | `in_duration_code` | IN | VARCHAR2 | — | Hearing duration code (e.g. `AM`, `PM`, `FULL`) | | `in_hear_loc` | IN | NUMBER | — | `hearing_location_seq_num` of the target hearing | | `in_alj` | IN | NUMBER | — | `alj_person_seq_num` of the target hearing | ### location_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (conflict count) | | **Called by** | `assignIt`, `assignItBalanced` | **Description:** Counts `schedule` rows (status `SET`/`RESET`) where the candidate attorney is already booked on `in_date` at a different hearing location than the target. Triggered when `location_flag = 'Y'`; result code `NALCC`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | | `in_date` | IN | DATE | — | Hearing date being evaluated | | `in_duration_code` | IN | VARCHAR2 | — | Hearing duration code (unused in the current query, retained for signature symmetry with the other conflict checks)* | | `in_hear_loc` | IN | NUMBER | — | `hearing_location_seq_num` of the target hearing | ### vacation_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (vacation-day count + out-of-office count) | | **Called by** | `assignIt`, `assignItBalanced` | **Description:** Computes two counts and sums them: (1) when `in_code = 'Y'`, counts `attorney_schedule` `LEAVE` rows of more than 4 days where today falls in the blackout window computed from the leave's `eff_date`/`exp_date` (the "don't assign 3 days before / 1 day after a 4+ day leave" rule); (2) counts overlapping `attorney_schedule` rows (any type) covering `in_date`, matching duration for `AM`/`PM` or any duration otherwise. Triggered when `calendar_flag` is `Y`, `H`, or `C`; result code `NAVAC`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | | `in_date` | IN | DATE | — | Hearing date being evaluated | | `in_duration_code` | IN | VARCHAR2 | — | Hearing duration code | | `in_code` | IN | VARCHAR2 | — | Calendar flag value passed from the caller (`Y`/`H`/`C`); only `Y` triggers the long-leave blackout check | ### case_load | Field | Value | | --- | --- | | **Returns** | NUMBER (weighted open case count) | | **Called by** | Not directly referenced elsewhere in this package body; exposed for external/legacy callers* | **Description:** Sums `case_weight_value * (100 / load_percent)` across the attorney's currently open (`case_status_code <> 'CLOSE'`) cases where the most recent `HEAR`/`MED` schedule row belongs to them, rounding to the nearest whole number. Returns 0 on divide-by-zero (a `load_percent` of 0) or no rows. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Attorney employee ID to compute case load for | ### period_case_load | Field | Value | | --- | --- | | **Returns** | NUMBER (weighted caseload + vacation-day adjustment for the assignment week) | | **Called by** | `get_assign_list` | **Description:** Computes the weighted open-case load (same formula as `case_load`) restricted to a 20-day window centered on the Friday of the hearing week (12 days before through 7 days after that Friday), then applies an out-of-town-rotation floor: if the attorney has a rotation assignment in that window with a minimum `rotation_average_case_count`, and their computed weekly load is below that floor, the floor is substituted. Finally adds `get_vacation_days` for the same 12/7-day window. Used to rank/display candidate workload on the assignment-list screen. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Attorney employee ID | | `hearingDate` | IN | DATE | — | Reference hearing date used to compute the assignment week | ### period_case_count | Field | Value | | --- | --- | | **Returns** | NUMBER (open case count for the assignment week) | | **Called by** | `get_assign_list` | **Description:** Counts (unweighted) the attorney's open `HEAR`/`MED` cases scheduled in the same 12-days-before/7-days-after Friday window used by `period_case_load`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Attorney employee ID | | `hearingDate` | IN | DATE | — | Reference hearing date used to compute the assignment week | ### get_weekly_case_load | Field | Value | | --- | --- | | **Returns** | NUMBER (weighted caseload for the trailing work-week) | | **Called by** | `calendar_review`, internal weekly-load rules in `assignIt`/`assignItBalanced` (via `load_result`/`get_hearing_days`) | **Description:** Sums weighted open-case load for `HEAR`/`MED` schedule rows in the 6-day window ending on the Friday of the hearing week (5 days before through that Friday). Used for the `weekly_case_load` conflict check and rendered as `weekly_load` on the calendar-review screen. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Attorney employee ID | | `hearingDate` | IN | DATE | — | Reference hearing date used to compute the work-week | ### get_hearing_days | Field | Value | | --- | --- | | **Returns** | NUMBER (distinct hearing days in the work-week, plus vacation days) | | **Called by** | `assignIt`, `assignItBalanced`, `calendar_review` | **Description:** Counts distinct scheduled `HEAR`/`MED` dates for the attorney in the same 5-day-trailing work-week as `get_weekly_case_load`, then adds `get_vacation_days` (5 days before, 0 after). In `assignIt`, if this total is below a threshold (1 day if `load_percent < 34`, 2 days if `< 67`, else no minimum) the `weekly_case_load` conflict is *not* raised — i.e. attorneys below their target hearing-day count for the week are still eligible. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Attorney employee ID | | `hearingDate` | IN | DATE | — | Reference hearing date | ### get_vacation_days | Field | Value | | --- | --- | | **Returns** | NUMBER (vacation/out-of-office day count in a caller-defined window)* | | **Called by** | `period_case_load`, `get_hearing_days`, `calendar_review` | **Description:** Counts `attorney_schedule` leave/out-of-office rows for the attorney within a window of `in_before` days before and `in_after` days after `hearingDate` (window derivation follows the same Friday-anchored pattern used elsewhere in the package).* Exact query body was not re-read in full for this pass; behavior inferred from call sites and parameter names.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Attorney employee ID | | `hearingDate` | IN | DATE | — | Reference hearing date | | `in_before` | IN | NUMBER | — | Days before the reference window to include | | `in_after` | IN | NUMBER | — | Days after the reference window to include | ### max_result_seq | Field | Value | | --- | --- | | **Returns** | NUMBER (current maximum `assignment_result_seq_num`) | | **Called by** | `clsAssignmentProcess` | **Description:** Returns `MAX(assignment_result_seq_num)` from `assignment_result`, or 0 if the table is empty. Used by the calling .NET code as a watermark/checkpoint to poll only newly-created assignment results (via the `in_num` parameter on `get_results`/`get_assign_results`/`getOutlookFields`). #### Parameters _None._ ### insert_results | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `assignIt`, `assignItBalanced` (internal) | **Description:** Inserts a new row into `assignment_result` using `assignment_result_seq.NEXTVAL` for the sequence, defaulting a null `in_schedule_num` to `2`, and stamping `create_date` with `SYSDATE`. Records both successful assignments (`CA`) and every blocking rule outcome (`NA*` codes). #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_case_seq` | IN | NUMBER | — | `legal_case_seq_num` of the case | | `in_rule_code` | IN | VARCHAR2 | — | Assignment rule code identifying which `assign_*` batch produced this result (e.g. `EA`, `ROT`, `SAME`) | | `in_schedule_num` | IN | NUMBER | — | `schedule_seq_num`; defaults to `2` if null | | `in_result_code` | IN | VARCHAR2 | — | Result code, e.g. `CA` (assigned) or an `NA*` blocking code | | `in_emplid` | IN | VARCHAR2 | — | Candidate/assigned attorney employee ID | ### rotation_conflict_check | Field | Value | | --- | --- | | **Returns** | NUMBER (conflict count) | | **Called by** | `assignIt`, `assignItBalanced` | **Description:** Counts `out_of_town_rotation_assgn` rows for the attorney where `in_date` falls within the rotation's effective window but the hearing's location code differs from the rotation's assigned location — i.e. the attorney is on out-of-town rotation duty for a different location that week. Triggered when `rotation_flag = 'Y'`; result code `NAROT`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | | `in_date` | IN | DATE | — | Hearing date | | `in_hear_code` | IN | VARCHAR2 | — | Hearing location code of the target hearing | ### same_time_loc_check | Field | Value | | --- | --- | | **Returns** | VARCHAR2 (employee ID, or `'NONE'`) | | **Called by** | `assign_same` (internal) | **Description:** Finds the attorney with the lowest employee ID already scheduled (`SET`/`RESET`) at the same ALJ, location, and overlapping duration/day span who is a `TRIAL` or `FED` attorney-profile type, so that other hearings at the same time/place can be batched to the same attorney. When `in_is_critical = 'Y'`, further restricts to attorneys flagged `ccu_claims_flag = 'Y'`; otherwise applies confidential-claims and DCBS/WCB claim-type eligibility filters (`in_wrkr_conflict`, `in_request_type`). #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_date` | IN | DATE | — | Hearing date | | `in_duration_code` | IN | VARCHAR2 | — | Hearing duration code | | `in_hear_loc` | IN | NUMBER | — | Hearing location sequence number | | `in_alj` | IN | NUMBER | — | ALJ person sequence number | | `in_is_critical` | IN | VARCHAR2 | — | `Y` if the case is flagged critical/CCU | | `in_request_type` | IN | VARCHAR2 | — | Claim request type code (e.g. `WCB`, `DCBS`) | | `in_wrkr_conflict` | IN | VARCHAR2 | — | Worker-conflict flag from the claim | ### assignIt | Field | Value | | --- | --- | | **Returns** | VARCHAR2(1) — `'Y'` if assigned, `'N'` if blocked | | **Called by** | `assign_by_employer`, `assign_rotation_cases`, `assign_same`, and the other `assign_*` batch procedures (internal) | **Description:** The core rule-evaluation function. Computes a holiday adjustment (via `sp72353_next_work_day`), a workload check (`get_hearing_days` vs. a load-percent-derived minimum), and, gated by the caller-supplied flags, the employer/adjuster/ALJ/attorney/rotation/calendar/vacation/location conflict checks described above. The unconditional `attorney_conflict_check` always runs. If every check returns zero, the case is assigned: `insert_results(,'CA',)`, `updateSchedule()`, and `Pk80001_Legalnet.updatecasestatus(,'ASSGN',)` are called and `'Y'` is returned. Otherwise every non-zero check inserts its corresponding `NA*` result row (in a fixed order: `NAHOL`, `NAEMP`, `NAADJ`, `NAALJ`, `NAATT`, `NAROT`, `NAC`, `NAVAC`, `LOAD`, `NALCC`) and `'N'` is returned. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_code` | IN | VARCHAR2 | — | Assignment rule code recorded on the result row (e.g. `EA`, `ROT`, `SAME`) | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | | `in_load_percent` | IN | NUMBER | — | Candidate's assignment load percentage, used to derive the minimum hearing-days threshold | | `in_assign_rec` | IN | `assignment_rec` | — | Record of case/schedule details and which conflict-check flags to apply | ### assignItBalanced | Field | Value | | --- | --- | | **Returns** | VARCHAR2(1) — `'Y'` if assigned, `'N'` if blocked | | **Called by** | `assign_*` batch procedures that use load-balanced candidate selection (internal)* | **Description:** Same conflict-evaluation and result-recording logic as `assignIt` (employer/adjuster/ALJ/attorney/rotation/calendar/vacation/holiday/load/location checks against the identical `assignment_rec` structure), used by assignment procedures that need to compare multiple candidates by load before committing.* The two functions share the identical rule set observed in source; any behavioral divergence beyond candidate-selection order was not confirmed.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_code` | IN | VARCHAR2 | — | Assignment rule code recorded on the result row | | `in_emplid` | IN | VARCHAR2 | — | Candidate attorney's employee ID | | `in_load_percent` | IN | NUMBER | — | Candidate's assignment load percentage | | `in_assign_rec` | IN | `assignment_rec` | — | Record of case/schedule details and conflict-check flags | ### assign_by_employer | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns `PENDA` cases with unassigned `SET`/`RESET` hearings whose claim's policy matches an active `attorney_policy_assignment` (directly, via policy relationship, or via rate account) to the policy's primary attorney, falling back to secondary then tertiary attorney, and finally re-attempting the primary attorney with all conflict flags forced off if none succeeded. Rule code `EA`. Cases are scoped to the current run via `CLM_DBA.temp_data_person_address.run_id = in_run_id`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check (`Y`/`H`/`C`) | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check when > 0 | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | | `in_run_id` | IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set via `temp_data_person_address.run_id` | ### assign_dcbs_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns a DCBS-specific subset of `PENDA` cases with unassigned hearings using the shared `assignIt`/`assignItBalanced` rule set.* Full body was not re-read line-by-line in this pass; behavior inferred from the procedure's position in the batch sequence and its identical flag signature to the other `assign_*` procedures.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | ### assign_recon_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns reconsideration cases (cases flagged via `request_issue_code.recon_issue_flag = 'Y'`) using the shared conflict-check rule set.* Full body was not re-read line-by-line in this pass.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | ### assign_rotation_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns `PENDA` cases with unassigned `SET`/`RESET` `HEAR` hearings whose location has an active `out_of_town_rotation_assgn` covering the hearing date, excluding cases with a reconsideration-flagged request issue. Assigns the rotation's designated attorney (`o.attorney_emplid`) with `rotation_flag` forced to `'N'` (the rotation itself is the assignment basis, so it is not re-checked as a conflict). Rule code `ROT`. Ordered by `schedule_date`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Accepted for signature symmetry; overridden to `'N'` internally before calling `assignIt`* | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | ### assign_same | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** For each unassigned `PENDA` case (excluding reconsideration-flagged issues) scoped to `in_run_id`, calls `same_time_loc_check` to find an attorney already booked at the same ALJ/location/time, and if one is found, assigns that attorney via `assignIt` with rule code `SAME` — batching hearings at the same time and place to a single attorney rather than distributing them independently. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Passed through to `assignIt` as the adjuster-check flag | | `alj_flag` | IN | VARCHAR2 | — | Passed through to `assignIt` as the ALJ-check flag | | `rotation_flag` | IN | VARCHAR2 | — | Passed through to `assignIt` as the rotation-check flag | | `calendar_flag` | IN | VARCHAR2 | — | Passed through to `assignIt` as the calendar-check flag | | `employer_flag` | IN | VARCHAR2 | — | Passed through to `assignIt` as the employer-check flag | | `weekly_case_load` | IN | NUMBER | — | Passed through to `assignIt` as the weekly-load-check flag | | `location_flag` | IN | VARCHAR2 | — | Passed through to `assignIt` as the location-check flag | | `in_run_id` | IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set | ### assign_health_issue_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns a health-issue-flagged subset of `PENDA` cases using the shared conflict-check rule set.* Full body was not re-read line-by-line in this pass; behavior inferred from the procedure's name and identical flag signature to the other `assign_*` procedures.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | ### assign_wrkr_conflict_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns cases flagged with a worker/confidential-claims conflict indicator (`wrkr_conflict_flag`) using the shared conflict-check rule set, scoped to `in_run_id`.* Full body was not re-read line-by-line in this pass beyond confirming its declaration and its shared `load_attorney_table` local helper pattern with `assign_critical_claim_cases` and `assign_remaining_cases`.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | | `in_run_id` | IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set | ### assign_critical_claim_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns critical/CCU-flagged claims, using an internal `load_attorney_table(schedule_date)` helper that pre-loads eligible attorney load/location data for the target hearing date before evaluating candidates via `assignIt`/`assignItBalanced`. Rule flags are supplied with an `in`-prefixed parameter naming convention distinct from the other `assign_*` procedures. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `inAdjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `inAlj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `inRotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `inCalendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `inEmployer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `inWeekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `inlocation_flag` | IN | VARCHAR2 | — | Enables the location conflict check | | `in_run_id` | IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set | ### assign_portland_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Assigns a Portland-location-specific subset of `PENDA` cases using the shared conflict-check rule set.* Full body was not re-read line-by-line in this pass beyond confirming its declaration position in the batch sequence.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | ### assign_remaining_cases | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Final catch-all pass over any `PENDA` cases with unassigned hearings not resolved by the earlier `assign_*` procedures, using an internal `load_attorney_table(schedule_date)` helper (same pattern as `assign_critical_claim_cases`/`assign_wrkr_conflict_cases`) and the shared conflict-check rule set, scoped to `in_run_id`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | | `in_run_id` | IN | VARCHAR2 | — | Batch run identifier scoping the candidate case set | ### previously_assigned | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `clsAssignmentProcess` | **Description:** Handles cases that were previously assigned to an attorney on an earlier hearing in the same legal case, likely re-assigning the same attorney to a subsequent hearing (consistent with the `get_previously_assigned` cursor's `CA`/`CAC`-filtered "most recent prior assignment" query pattern).* Full body was not re-read line-by-line in this pass.** #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `adjuster_flag` | IN | VARCHAR2 | — | Enables the adjuster conflict check | | `alj_flag` | IN | VARCHAR2 | — | Enables the ALJ conflict check | | `rotation_flag` | IN | VARCHAR2 | — | Enables the rotation conflict check | | `calendar_flag` | IN | VARCHAR2 | — | Enables the calendar/vacation conflict check | | `employer_flag` | IN | VARCHAR2 | — | Enables the employer conflict check | | `weekly_case_load` | IN | NUMBER | — | Enables the weekly-load conflict check | | `location_flag` | IN | VARCHAR2 | — | Enables the location conflict check | ### get_results | Field | Value | | --- | --- | | **Returns** | REF CURSOR `assign_results_cursor` (`assign_results_rec`) via `recordset` | | **Called by** | `clsAssignmentProcess` | **Description:** Returns assignment-result rows joined to attorney name, rule description, and result description, filtered by `in_type`: `ASSIGN` returns rows with `assignment_result_seq_num > in_num` (new-since-watermark), `DATE` returns rows created on `in_date`, and `CASE` returns all rows for the case identified by `in_num`. Ordered by case then result sequence. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_num` | IN | NUMBER | — | Watermark sequence number (`ASSIGN`) or `legal_case_seq_num` (`CASE`); ignored for `DATE` | | `in_type` | IN | VARCHAR2 | — | Query mode: `ASSIGN`, `DATE`, or `CASE` | | `in_date` | IN | DATE | — | Result creation date filter, used only when `in_type = 'DATE'` | | `recordset` | OUT | `assign_results_cursor` | — | REF CURSOR of `assign_results_rec` rows | ### get_assign_results | Field | Value | | --- | --- | | **Returns** | REF CURSOR `assign_header_cursor` (`assign_header_rec`) via `recordset` | | **Called by** | `clsAssignmentProcess` | **Description:** Returns a richer header view of the most recent assignment result per case (joined to schedule, hearing location, ALJ, and claim), split via `UNION` into successfully assigned (`CA`/`CAC`) and not-assigned rows, filtered by `in_type` (`ASSIGN`/`DATE`/others follow the same pattern as `get_results`). Used to populate the assignment-results review screen, including "Not Assigned" placeholder attorney names for unresolved rows. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | `assign_header_cursor` | — | REF CURSOR of `assign_header_rec` rows | | `in_num` | IN | NUMBER | — | Watermark/filter value, meaning depends on `in_type` | | `in_type` | IN | VARCHAR2 | — | Query mode (`ASSIGN`, `DATE`, or other supported mode value) | | `in_date` | IN | DATE | — | Result date filter, used for date-scoped modes | ### get_assign_list | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAssignmentProcess` | **Description:** Returns the list of hearings assigned on `in_date`, including in-house attorney assignments (joined to `period_case_count`/`period_case_load` for that attorney's workload) unioned with outside-counsel assignments (`asgn_other_firm_seq_num IS NOT NULL`, reported as `'Outside Counsel'`). Only rows where the most recent assignment result is `CA`/`CAC` (or has no result row yet, defaulted to `CA`) and the schedule's `assigned_date` matches the result's `create_date` are included — i.e. only the current, still-valid assignment per case. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | REF CURSOR of assignment-list rows | | `in_date` | IN | DATE | — | Assignment date to list | ### get_previously_assigned | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAssignmentProcess` | **Description:** Returns, for each new (`assignment_result_seq_num > in_num`) `CA`/`CAC` assignment result, the attorney and hearing details of the case's immediately prior scheduled hearing (the max `schedule_seq_num` less than the current one) — used to detect and continue a case with the same previously-assigned attorney. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_num` | IN | NUMBER | — | Watermark sequence number for new assignment results | | `recordset` | OUT | ref_cursor | — | REF CURSOR of prior-hearing/attorney rows | ### updateSchedule | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | `assignIt`, `assignItBalanced` (internal) | **Description:** Updates the `schedule` row identified by `schedSeqNum`, setting the assigned attorney, assignment date, and audit update date/user. Called immediately after a successful `assignIt`/`assignItBalanced` evaluation to persist the winning attorney. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `schedSeqNum` | IN | NUMBER | — | `schedule_seq_num` of the hearing being updated | | `attyEmplid` | IN | VARCHAR2 | — | Employee ID of the newly assigned attorney | | `asgnDate` | IN | DATE | — | Assignment date (`SYSDATE` at call time) | | `updateDate` | IN | DATE | — | Audit update date (`SYSDATE` at call time) | | `updateUser` | IN | VARCHAR2 | — | Audit update user (literal `'Assignment'` at call time) | ### calendar_review | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAssignmentProcess` | **Description:** Returns, for every active attorney profile (`exp_date IS NULL`) at the requested location (or all locations when `in_loc_code = 'ALL'`), their schedule entry for `in_date` (if any, via a left outer join so unscheduled attorneys still appear), computed weekly load, period case load, vacation days, and hearing days — the data set behind the attorney calendar-review grid. Location filtering matches the attorney's primary, secondary, or tertiary assigned location. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_loc_code` | IN | VARCHAR2 | — | Hearing location code to filter by, or `'ALL'` for every location | | `in_date` | IN | DATE | — | Calendar date to review | | `recordset` | OUT | ref_cursor | — | REF CURSOR of attorney/schedule/workload rows | ### getOutlookFields | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAssignmentProcess` | **Description:** Returns hearing/schedule fields formatted for Outlook calendar synchronization, in one of three modes selected by `inType`: `CASE` returns all future (within the last 3 months and beyond) scheduled hearings for a single case (`aNum` = `legal_case_seq_num`); `ASSIGN` returns hearings for new (`assignment_result_seq_num > aNum`) current assignment results; `VERIFY` returns each case's current most-recent future hearing. All three join to consolidated-case data and include ALJ name, location, duration, assigned attorney's user ID (Outlook mail alias), schedule text, and case weight. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | REF CURSOR of Outlook-field rows | | `inType` | IN | VARCHAR2 | — | Query mode: `CASE`, `ASSIGN`, or `VERIFY` | | `aNum` | IN | NUMBER | — | `legal_case_seq_num` (`CASE` mode) or watermark sequence (`ASSIGN` mode); unused in `VERIFY` mode | ### getAttorneyPastHearings | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAttorneyConflict` | **Description:** Returns hearings scheduled in the past (`schedule_date < TRUNC(SYSDATE)`, status `SET`/`RESET`) for the case's most recent hearing. When `inUserid = 'OUTCOU'`, returns past outside-counsel (`other_firm`) hearings across all cases; otherwise returns past hearings for the in-house attorney identified by `inUserid` (`adp_employee.user_id`), ordered by scheduled date. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | REF CURSOR of past-hearing rows | | `inUserid` | IN | VARCHAR2 | — | Attorney user ID, or the literal `'OUTCOU'` to select outside-counsel hearings | ### getAttorneyConflictHearings | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAttorneyConflict` | **Description:** Detects future (`schedule_date > TRUNC(SYSDATE)`) same-time/same-place hearing conflicts on open (`case_status_code <> 'CLOSE'`) cases: same-day non-multi-day (`d.days_num = 0`) hearings at the same ALJ/location assigned to a *different* attorney than the requester (excluding `APEAL`-type attorney profiles, i.e. reconsideration cases), unioned with a symmetrical multi-day overlap query. Used to flag scheduling conflicts on the attorney-conflict screen when `inUserid = 'CONFLICT'`. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | REF CURSOR of conflict-hearing rows | | `inUserid` | IN | VARCHAR2 | — | Must equal the literal `'CONFLICT'` to trigger the query; otherwise no cursor is opened* | ### getAttorneyScheduleConflict | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAttorneySchedule` | **Description:** Returns, for the attorney identified by `inUserid`, every future (`schedule_date > TRUNC(SYSDATE)`) hearing/event on an open case that overlaps another schedule entry for the same attorney at a different ALJ or location (same-day and multi-day duration variants), plus overlapping `attorney_schedule` "Time Off" entries (day-based and multi-day variants) — a consolidated double-booking and time-off conflict report for a single attorney, ordered by scheduled date. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | REF CURSOR of schedule-conflict rows | | `inUserid` | IN | VARCHAR2 | — | Attorney user ID (`adp_employee.user_id`) to check for conflicts | ### getConsolFileLocation | Field | Value | | --- | --- | | **Returns** | REF CURSOR `ref_cursor` via `recordset` | | **Called by** | `clsAssignmentProcess` | **Description:** For a primary consolidated case (`inLegalCaseId`), returns each secondary case whose most-recent physical legal-file location (attorney/location, both by max `move_date`) differs from the primary case's most-recent file location — identifying consolidated cases whose paper files are not co-located with the lead case's file. #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `inLegalCaseId` | IN | NUMBER | — | `legal_case_seq_num` of the primary (consolidating) case | | `recordset` | OUT | ref_cursor | — | REF CURSOR of mismatched secondary-case/location rows | ### Send_Assignment_Info | Field | Value | | --- | --- | | **Returns** | None (procedure) | | **Called by** | Not referenced elsewhere in this package body; likely invoked from an external batch-monitoring job* | **Description:** Sends an email via `pk10001_email.send`, from `gremil@saif.com` to a fixed distribution list (`gremil@saif.com`, `timfor@saif.com`, `brisly@saif.com`), with a fixed subject of "Attorney Assignment Info" and the caller-supplied body text — used to notify the legal-operations team of batch assignment run status or exceptions.* #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `inText` | IN | VARCHAR2 | — | Email body text | ## Business Rules Rules extracted from this procedure's logic: | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | [BR-PKG2-001](../business-rules.md#br-pkg2-001-employer-conflict-blocks-assignment) | Employer conflict blocks assignment | `employer_flag = 'Y'` and the candidate attorney has an active `attorney_conflict` row for the claim's policy number | Increment `employer_result`; if final result is non-zero, record `NAEMP` and do not assign | | [BR-PKG2-002](../business-rules.md#br-pkg2-002-adjuster-conflict-blocks-assignment) | Adjuster conflict blocks assignment | `adjuster_flag = 'Y'` and the candidate attorney has an active conflict tied to the case's assigned adjuster (matched by `user_id`) | Increment `adjuster_result`; record `NAADJ` and do not assign | | [BR-PKG2-003](../business-rules.md#br-pkg2-003-alj-conflict-blocks-assignment) | ALJ conflict blocks assignment | `alj_flag = 'Y'` and the candidate attorney has an active conflict tied to the hearing's ALJ (`conflict_legal_person_seq_num`) | Increment `alj_result`; record `NAALJ` and do not assign | | [BR-PKG2-004](../business-rules.md#br-pkg2-004-opposing-representation-conflict-always-checked) | Opposing-representation conflict always checked | Candidate's `atty_provider_location_seq_num` matches the claimant's worker-attorney firm location on the case (checked unconditionally, no flag gate) | Increment `attorney_result`; record `NAATT` and do not assign | | [BR-PKG2-005](../business-rules.md#br-pkg2-005-rotation-duty-blocks-off-rotation-assignment) | Rotation duty blocks off-rotation assignment | `rotation_flag = 'Y'` and the candidate is on `out_of_town_rotation_assgn` duty for a different location during the hearing's effective window | Increment `rotation_result`; record `NAROT` and do not assign | | [BR-PKG2-006](../business-rules.md#br-pkg2-006-calendar-double-booking-blocks-assignment) | Calendar double-booking blocks assignment | `calendar_flag` in (`Y`,`H`,`C`) and the candidate has an overlapping `SET`/`RESET` schedule entry at a different ALJ or location for the same date/duration | Increment `calendar_result`; record `NAC` and do not assign | | [BR-PKG2-007](../business-rules.md#br-pkg2-007-long-leave-blackout-window-blocks-assignment) | Long-leave blackout window blocks assignment | `calendar_flag` in (`Y`,`H`,`C`), `in_code = 'Y'`, and today falls within 3 days before through 1 day after a `LEAVE` entry of more than 4 days | Increment `vacation_result`; record `NAVAC` and do not assign | | [BR-PKG2-008](../business-rules.md#br-pkg2-008-location-conflict-blocks-assignment) | Location conflict blocks assignment | `location_flag = 'Y'` and the candidate is already scheduled elsewhere on the same date at a different hearing location | Increment `location_result`; record `NALCC` and do not assign | | [BR-PKG2-009](../business-rules.md#br-pkg2-009-weekly-caseload-floor-exempts-low-load-attorneys) | Weekly caseload floor exempts low-load attorneys from the load check | `weekly_case_load > 0` and the attorney's `get_hearing_days` count is below a load-percent-derived minimum (1 day if `in_load_percent < 34`, 2 days if `< 67`) | `load_result` is reset to 0, so `LOAD` is not recorded and the attorney remains eligible | | [BR-PKG2-010](../business-rules.md#br-pkg2-010-holiday-shifted-hearing-date-is-flagged-but-non-blocking) | Holiday-shifted hearing date flags a result but never blocks assignment | `sp72353_next_work_day(schedule_date) <> schedule_date` (i.e. the hearing date is a non-work day) | Increment `holiday_result` and record `NAHOL` if the attorney is otherwise not assigned, but `holiday_result` is excluded from the assignment-success determination in the observed rule set* | | [BR-PKG2-011](../business-rules.md#br-pkg2-011-successful-assignment-updates-case-status) | Successful assignment updates case status | All gated conflict-check results sum to zero for the candidate | `insert_results(,'CA',)`, `updateSchedule()`, and `Pk80001_Legalnet.updatecasestatus(,'ASSGN',)` are called, and the schedule row's assigned attorney/date are persisted | | [BR-PKG2-012](../business-rules.md#br-pkg2-012-assignment-result-codes-distinguish-success-from-every-blocking-reason) | Assignment-result codes distinguish success from every blocking reason | Result code `CA`/`CAC` indicates a completed assignment; codes `NAEMP`, `NAADJ`, `NAALJ`, `NAATT`, `NAROT`, `NAC`, `NAVAC`, `NAHOL`, `LOAD`, `NALCC` each identify a single specific blocking rule | Downstream cursors (`get_results`, `get_assign_results`, `get_assign_list`) filter or interpret rows using these codes to distinguish assigned vs. not-assigned cases | > These rules are documented in [business-rules.md](../business-rules.md). Newly introduced rules must be added to `business-rules.md` as part of the same writing task. ## Operations Operations and processes implemented in this procedure: | Operation | Description | Trigger | | --- | --- | --- | | Run attorney assignment batch | Sequentially executes `assign_by_employer`, `assign_dcbs_cases`, `assign_recon_cases`, `assign_rotation_cases`, `assign_same`, `assign_health_issue_cases`, `assign_wrkr_conflict_cases`, `assign_critical_claim_cases`, `assign_portland_cases`, `assign_remaining_cases`, and `previously_assigned` over `PENDA` cases with unassigned `SET`/`RESET` hearings, evaluating each candidate attorney against the conflict-check rule set via `assignIt`/`assignItBalanced` and recording an assignment result for every outcome | Scheduled/batch invocation of the legal assignment process (`clsAssignmentProcess`) | | Review assignment results | Retrieves newly created or historical assignment results (success and blocking reasons) via `get_results`, `get_assign_results`, and `get_assign_list` for display on the assignment-review screen | User opens the assignment-results or assignment-list screen in the Legal application | | Attorney calendar review | Builds the per-attorney calendar grid (schedule, weekly load, case load, vacation days, hearing days) across one or all hearing locations via `calendar_review` | User opens the calendar-review screen | | Sync hearings to Outlook | Supplies case/assignment/schedule fields formatted for Outlook calendar entries via `getOutlookFields`, in case, new-assignment, or verification modes | Outlook calendar synchronization job or screen refresh | | Attorney conflict and schedule review | Surfaces past hearings, same-time/place conflicts, and general schedule double-bookings for a given attorney via `getAttorneyPastHearings`, `getAttorneyConflictHearings`, and `getAttorneyScheduleConflict` | User opens the attorney-conflict or attorney-schedule screen | | Consolidated case file-location check | Identifies secondary cases in a consolidation whose physical file location differs from the primary case via `getConsolFileLocation` | User opens the consolidated-case file-location screen | > These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document. ## Source Code
PL/SQL package specification ```sql -- Actual PL/SQL spec retrieved from Oracle via Legends CLI -- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk80005_assignment PACKAGE pk80005_ASSIGNMENT AS TYPE ref_cursor IS REF CURSOR; TYPE attorney_rec IS RECORD( emplid VARCHAR2(11), primary_loc VARCHAR2(5), secondary_loc VARCHAR2(5), tertiary_loc VARCHAR2(5), load_percent NUMBER, case_load NUMBER, attorney_last_name VARCHAR2(40)); TYPE attorney_tab IS TABLE OF attorney_rec INDEX BY BINARY_INTEGER; TYPE assignment_rec IS RECORD( legal_case_seq_num NUMBER, schedule_date DATE, hearing_location_seq_num NUMBER, alj_person_seq_num NUMBER, duration_code VARCHAR2(5), schedule_seq_num NUMBER, hearing_location_code VARCHAR2(5), primary_emplid VARCHAR2(11), secondary_emplid VARCHAR2(11), tertiary_emplid VARCHAR2(11), adjuster_flag VARCHAR2(1), alj_flag VARCHAR2(1), rotation_flag VARCHAR2(1), calendar_flag VARCHAR2(1), employer_flag VARCHAR2(1), weekly_case_load NUMBER, location_flag VARCHAR2(1), wcb_num VARCHAR2(10)); TYPE assign_cursor IS REF CURSOR RETURN assignment_rec; FUNCTION adjuster_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER; FUNCTION employer_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER; FUNCTION attorney_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER; FUNCTION alj_conflict_check (in_alj_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER; FUNCTION calendar_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_duration_code IN VARCHAR2, in_hear_loc IN NUMBER, in_alj IN NUMBER) RETURN NUMBER; FUNCTION location_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_duration_code IN VARCHAR2, in_hear_loc IN NUMBER) RETURN NUMBER; FUNCTION vacation_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_duration_code IN VARCHAR2, in_code IN VARCHAR2) RETURN NUMBER; FUNCTION case_load(in_emplid IN VARCHAR2) RETURN NUMBER; FUNCTION period_case_load(in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER; FUNCTION period_case_count(in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER; FUNCTION get_weekly_case_load(in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER; FUNCTION get_hearing_days(in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER; FUNCTION get_vacation_days(in_emplid IN VARCHAR2, hearingDate IN DATE, in_before IN NUMBER, in_after IN NUMBER) RETURN NUMBER; FUNCTION max_result_seq RETURN NUMBER; PROCEDURE insert_results (in_case_seq IN NUMBER, in_rule_code IN VARCHAR2, in_schedule_num IN NUMBER, in_result_code IN VARCHAR2, in_emplid IN VARCHAR2); FUNCTION rotation_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_hear_code IN VARCHAR2) RETURN NUMBER; FUNCTION same_time_loc_check (in_date IN DATE, in_duration_code IN VARCHAR2, in_hear_loc IN NUMBER, in_alj IN NUMBER, in_is_critical IN VARCHAR2, in_request_type IN VARCHAR2, in_wrkr_conflict IN VARCHAR2) RETURN VARCHAR2; FUNCTION assignIt(in_code IN VARCHAR2, in_emplid IN VARCHAR2, in_load_percent IN NUMBER, in_assign_rec IN assignment_rec) RETURN VARCHAR2; FUNCTION assignItBalanced(in_code IN VARCHAR2, in_emplid IN VARCHAR2, in_load_percent IN NUMBER, in_assign_rec IN assignment_rec) RETURN VARCHAR2; PROCEDURE assign_by_employer(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2); PROCEDURE assign_dcbs_cases(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2); PROCEDURE assign_recon_cases(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2); PROCEDURE assign_rotation_cases(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2); PROCEDURE assign_same(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2); PROCEDURE assign_health_issue_cases(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2); PROCEDURE assign_wrkr_conflict_cases(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2); PROCEDURE assign_critical_claim_cases(inAdjuster_flag IN VARCHAR2, inAlj_flag IN VARCHAR2, inRotation_flag IN VARCHAR2, inCalendar_flag IN VARCHAR2, inEmployer_flag IN VARCHAR2, inWeekly_case_load IN NUMBER, inlocation_flag IN VARCHAR2, in_run_id IN VARCHAR2); PROCEDURE assign_portland_cases(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2); PROCEDURE assign_remaining_cases(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2); PROCEDURE previously_assigned(adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2); TYPE assign_results_rec IS RECORD( asgn_result_seq_num NUMBER, legal_case_seq_num NUMBER, schedule_seq_num NUMBER, case_num VARCHAR2(8), rule_descr VARCHAR2(65), result_descr VARCHAR2(65), asgn_attorney_emplid VARCHAR2(11), asgn_attorney_name VARCHAR2(65)); TYPE assign_results_cursor IS REF CURSOR RETURN assign_results_rec; PROCEDURE get_results(in_num IN NUMBER, in_type IN VARCHAR2, in_date IN DATE, recordset OUT assign_results_cursor); TYPE assign_header_rec IS RECORD( asgn_result_seq_num NUMBER, legal_case_seq_num NUMBER, schedule_seq_num NUMBER, case_num VARCHAR2(8), asgn_attorney_emplid VARCHAR2(11), asgn_attorney_name VARCHAR2(65), hearing_date DATE, create_date DATE, hearing_location VARCHAR2(65), result_code VARCHAR2(5), email_address VARCHAR2(30), alj_name VARCHAR2(50), schedule_time VARCHAR2(8), claim_id VARCHAR2(25)); TYPE assign_header_cursor IS REF CURSOR RETURN assign_header_rec; PROCEDURE get_assign_results(recordset OUT assign_header_cursor, in_num IN NUMBER, in_type IN VARCHAR2, in_date IN DATE); PROCEDURE get_assign_list(recordset OUT ref_cursor, in_date IN DATE); PROCEDURE get_previously_assigned(in_num IN NUMBER, recordset OUT ref_cursor); PROCEDURE updateSchedule(schedSeqNum IN NUMBER, attyEmplid IN VARCHAR2,asgnDate IN DATE, updateDate IN DATE, updateUser IN VARCHAR2); PROCEDURE calendar_review(in_loc_code IN VARCHAR2, in_date IN DATE, recordset OUT ref_cursor); PROCEDURE getOutlookFields(recordset OUT ref_cursor, inType IN VARCHAR2, aNum IN NUMBER); PROCEDURE getAttorneyPastHearings(recordset OUT ref_cursor, inUserid IN VARCHAR2); PROCEDURE getAttorneyConflictHearings(recordset OUT ref_cursor, inUserid IN VARCHAR2); PROCEDURE getAttorneyScheduleConflict(recordset OUT ref_cursor, inUserid IN VARCHAR2); PROCEDURE getConsolFileLocation(inLegalCaseId IN NUMBER, recordset OUT ref_cursor); PROCEDURE Send_Assignment_Info(inText IN VARCHAR2); END; ```
PL/SQL package/procedure body ```sql -- Actual PL/SQL body retrieved from Oracle via Legends CLI -- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk80005_assignment PACKAGE BODY pk80005_assignment AS FUNCTION adjuster_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER AS v_conflict_count NUMBER; BEGIN BEGIN v_conflict_count := 0; SELECT COUNT (*) INTO v_conflict_count FROM attorney_conflict a, legal_case l, CLM_DBA.temp_data_person_address c, adp_employee e WHERE l.legal_case_seq_num = in_Case_seq AND a.exp_date IS NULL AND l.claim_id = c.clm_id AND a.attorney_emplid = in_emplid AND TRIM (c.adjuster_user_id) = TRIM (e.user_id) AND e.emplid = a.adjuster_emplid; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; IF v_conflict_count IS NULL THEN v_conflict_count := 0; END IF; RETURN v_conflict_count; END adjuster_conflict_check; FUNCTION employer_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER AS v_conflict_count NUMBER; BEGIN BEGIN v_conflict_count := 0; SELECT COUNT (*) INTO v_conflict_count FROM attorney_conflict a, legal_case l, CLM_DBA.temp_data_person_address c WHERE l.legal_case_seq_num = in_Case_seq AND a.attorney_emplid = in_emplid AND a.EXP_DATE IS NULL AND l.claim_id = c.clm_id AND C.POLICY_NUM = a.POLICY_NUM; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; IF v_conflict_count IS NULL THEN v_conflict_count := 0; END IF; RETURN v_conflict_count; END employer_conflict_check; FUNCTION alj_conflict_check (in_alj_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER AS v_conflict_count NUMBER; BEGIN BEGIN v_conflict_count := 0; SELECT COUNT (*) INTO v_conflict_count FROM attorney_conflict WHERE conflict_legal_person_seq_num = in_ALJ_seq AND exp_date IS NULL AND attorney_emplid = in_emplid; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; IF v_conflict_count IS NULL THEN v_conflict_count := 0; END IF; RETURN v_conflict_count; END alj_conflict_check; FUNCTION attorney_conflict_check (in_case_seq IN NUMBER, in_emplid IN VARCHAR2) RETURN NUMBER AS v_conflict_count NUMBER; BEGIN BEGIN v_conflict_count := 0; SELECT COUNT (*) INTO v_conflict_count FROM attorney_conflict a, legal_case l WHERE l.legal_case_seq_num = in_Case_seq AND a.attorney_emplid = in_emplid AND a.EXP_DATE IS NULL AND A.ATTY_PROVIDER_LOCATION_SEQ_NUM = L.WORKER_ATTY_FIRM_PROV_LOC_ID; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; IF v_conflict_count IS NULL THEN v_conflict_count := 0; END IF; RETURN v_conflict_count; END attorney_conflict_check; PROCEDURE assign_by_employer (Adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2) IS v_assigned VARCHAR2 (1); assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; BEGIN OPEN assigns_cursor FOR SELECT l.legal_case_seq_num, schedule_date, s.hearing_location_seq_num, alj_person_seq_num, duration_code, schedule_seq_num, h.hearing_location_code, primary_emplid, NVL (secondary_emplid, 'NONE') AS secondary_emplid, NVL (tertiary_emplid, 'NONE') AS tertiary_emplid, adjuster_flag, alj_flag, rotation_flag, Calendar_flag, employer_flag, weekly_case_load, location_flag, wcb_num FROM legal_case l, CLM_DBA.temp_data_person_address c, hearing_location h, attorney_policy_assignment a, schedule s, policy p WHERE l.claim_id = c.clm_id AND c.run_id = in_run_id AND l.legal_case_seq_num = s.legal_case_seq_num AND c.policy_num = p.policy_num AND ( p.policy_num = a.policy_num OR P.POLICY_RELATIONSHIP_POLICY_NUM = A.POLICY_NUM OR P.RATE_ACCOUNT = A.POLICY_NUM) AND l.case_status_code = 'PENDA' AND s.schedule_type_code = 'HEAR' AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') AND asgn_attorney_emplid IS NULL AND asgn_other_firm_seq_num IS NULL AND s.hearing_location_seq_num = h.hearing_location_seq_num AND a.exp_date IS NULL; LOOP FETCH assigns_cursor INTO assign_rec; EXIT WHEN assigns_cursor%NOTFOUND; v_assigned := assignIt ('EA', assign_rec.primary_emplid, 100, assign_rec); IF v_assigned = 'N' AND assign_rec.secondary_emplid <> 'NONE' THEN v_assigned := assignIt ('EA', assign_rec.secondary_emplid, 100, assign_rec); END IF; IF v_assigned = 'N' AND assign_rec.tertiary_emplid <> 'NONE' THEN v_assigned := assignIt ('EA', assign_rec.tertiary_emplid, 100, assign_rec); END IF; IF v_assigned = 'N' THEN assign_rec.adjuster_flag := 'N'; assign_rec.alj_flag := 'N'; assign_rec.calendar_flag := 'N'; assign_rec.employer_flag := 'N'; assign_rec.rotation_flag := 'N'; assign_rec.weekly_case_load := 0; assign_rec.location_flag := 'N'; v_assigned := assignIt ('EA', assign_rec.primary_emplid, 100, assign_rec); END IF; END LOOP; CLOSE assigns_cursor; END assign_by_employer; PROCEDURE assign_critical_claim_cases (inAdjuster_flag IN VARCHAR2, inAlj_flag IN VARCHAR2, inRotation_flag IN VARCHAR2, inCalendar_flag IN VARCHAR2, inEmployer_flag IN VARCHAR2, inWeekly_case_load IN NUMBER, inlocation_flag IN VARCHAR2, in_run_id IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_index NUMBER; v_cnt NUMBER; v_first NUMBER; v_start NUMBER; last_date DATE; last_alj NUMBER := 0; last_duration VARCHAR2 (5); last_emplid VARCHAR2 (11); attorney_data attorney_tab; assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; PROCEDURE load_attorney_table (schedule_date IN DATE) IS CURSOR attorney_cursor IS SELECT profile_emplid, primary_hear_loc_code, NVL (secondary_hear_loc_code, 'NONE'), NVL (tertiary_hear_loc_code, 'NONE'), load_percent, period_case_load (profile_emplid, schedule_date) FROM attorney_profile WHERE ccu_claims_flag = 'Y' AND ( attorney_type_code = 'TRIAL' OR attorney_type_code = 'FED') AND load_percent > 0 ORDER BY 2, 5; BEGIN OPEN attorney_cursor; v_index := 1; LOOP FETCH attorney_cursor INTO attorney_data (v_index).emplid, attorney_data (v_index).primary_loc, attorney_data (v_index).secondary_loc, attorney_data (v_index).tertiary_loc, attorney_data (v_index).load_percent, attorney_data (v_index).case_load; EXIT WHEN attorney_cursor%NOTFOUND; v_index := v_index + 1; END LOOP; v_cnt := attorney_data.COUNT; v_index := 1; v_start := 1; CLOSE attorney_cursor; END load_attorney_table; BEGIN OPEN assigns_cursor FOR SELECT /*+ FIRST_ROWS */ l.legal_case_seq_num, s.schedule_date, s.hearing_location_seq_num, s.alj_person_seq_num, s.duration_code, s.schedule_seq_num, h.hearing_location_code, 'none' AS primary_emplid, 'none' AS secondary_emplid, 'none' AS tertiary_emplid, inAdjuster_flag, inAlj_flag, inRotation_flag, inCalendar_flag, inEmployer_flag, inWeekly_case_load, inlocation_flag, wcb_num FROM legal_case l, schedule s, hearing_location h, CLM_DBA.temp_data_person_address Clm WHERE l.legal_case_seq_num = s.legal_case_seq_num AND l.case_status_code = 'PENDA' AND s.schedule_type_code = 'HEAR' AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') AND asgn_attorney_emplid IS NULL AND asgn_other_firm_seq_num IS NULL AND s.hearing_location_seq_num = h.hearing_location_seq_num AND l.request_type_code = 'WCB' AND l.claim_id = clm.clm_id AND clm.run_id = in_run_id AND clm.is_critical = 'Y' AND NOT EXISTS (SELECT 1 FROM request_issue r, request_issue_code c WHERE r.legal_case_seq_num = l.legal_case_seq_num AND r.request_issue_code = c.code AND c.recon_issue_flag = 'Y') ORDER BY schedule_date, alj_person_seq_num, duration_code; LOOP FETCH assigns_cursor INTO assign_rec; EXIT WHEN assigns_cursor%NOTFOUND; v_first := 0; v_assigned := 'N'; IF last_alj = assign_rec.alj_person_seq_num AND last_date = assign_rec.schedule_date AND last_duration = assign_rec.duration_code THEN v_assigned := assignIt ('SAME', last_emplid, 100, assign_rec); ELSE load_attorney_table (assign_rec.schedule_date); FOR v_index IN v_start .. v_cnt LOOP IF attorney_data (v_index).primary_loc = assign_rec.hearing_location_code THEN v_assigned := assignIt ('CRTCL', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN v_start := v_index + 1; last_emplid := attorney_data (v_index).emplid; IF v_start > v_cnt THEN v_start := 1; END IF; EXIT; END IF; END IF; END LOOP; IF v_assigned = 'N' AND v_start > 1 THEN FOR v_index IN 1 .. v_start - 1 LOOP IF attorney_data (v_index).primary_loc = assign_rec.hearing_location_code THEN v_assigned := assignIt ('CRTCL', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN v_start := v_index + 1; last_emplid := attorney_data (v_index).emplid; IF v_start > v_cnt THEN v_start := 1; END IF; EXIT; END IF; END IF; END LOOP; END IF; IF v_assigned = 'N' THEN FOR v_index IN 1 .. v_cnt LOOP IF attorney_data (v_index).secondary_loc = assign_rec.hearing_location_code THEN v_assigned := assignIt ('CRTCL', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN last_emplid := attorney_data (v_index).emplid; EXIT; END IF; END IF; END LOOP; END IF; IF v_assigned = 'N' THEN FOR v_index IN 1 .. v_cnt LOOP IF attorney_data (v_index).tertiary_loc = assign_rec.hearing_location_code THEN v_assigned := assignIt ('CRTCL', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN last_emplid := attorney_data (v_index).emplid; EXIT; END IF; END IF; END LOOP; END IF; -- try to assign to first atty without looking at case load IF v_assigned = 'N' THEN IF v_first > 0 THEN assign_rec.Weekly_case_load := 0; assign_rec.location_flag := 'N'; v_assigned := assignIt ('CRTCL', attorney_data (v_first).emplid, attorney_data (v_first).load_percent, assign_rec); last_emplid := attorney_data (v_first).emplid; ELSE insert_results (assign_rec.legal_case_seq_num, 'CRTCL', assign_rec.schedule_seq_num, 'NALOC', '0'); END IF; END IF; END IF; IF v_assigned = 'Y' THEN last_date := assign_rec.schedule_date; last_alj := assign_rec.alj_person_seq_num; last_duration := assign_rec.duration_code; END IF; END LOOP; CLOSE assigns_cursor; END assign_critical_claim_cases; PROCEDURE assign_dcbs_cases (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_index NUMBER; v_cnt NUMBER; v_first NUMBER; v_start NUMBER := 1; last_date DATE; last_alj NUMBER := 0; last_duration VARCHAR2 (5); last_emplid VARCHAR2 (11); attorney_data attorney_tab; assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; no_longer_active EXCEPTION; PRAGMA EXCEPTION_INIT (no_longer_active, -20001); BEGIN RAISE no_longer_active; END assign_dcbs_cases; PROCEDURE assign_health_issue_cases (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_index NUMBER; v_cnt NUMBER; v_first NUMBER; last_date DATE; last_alj NUMBER := 0; last_duration VARCHAR2 (5); last_emplid VARCHAR2 (11); assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; attorney_data attorney_tab; no_longer_active EXCEPTION; PRAGMA EXCEPTION_INIT (no_longer_active, -20001); BEGIN RAISE no_longer_active; END assign_health_issue_cases; PROCEDURE assign_portland_cases (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_index NUMBER; v_cnt NUMBER; v_first NUMBER; v_start NUMBER; last_date DATE; last_alj NUMBER := 0; last_duration VARCHAR2 (5); last_emplid VARCHAR2 (11); assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; attorney_data attorney_tab; no_longer_active EXCEPTION; PRAGMA EXCEPTION_INIT (no_longer_active, -20001); BEGIN RAISE no_longer_active; END assign_portland_cases; PROCEDURE assign_recon_cases (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_last VARCHAR2 (11); v_index NUMBER; v_cnt NUMBER; v_first NUMBER; attorney_data attorney_tab; assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; no_longer_active EXCEPTION; PRAGMA EXCEPTION_INIT (no_longer_active, -20001); BEGIN RAISE no_longer_active; END assign_recon_cases; PROCEDURE assign_remaining_cases (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2) IS v_email_text VARCHAR2 (4000) := ''; v_case_count NUMBER := 0; v_assigned VARCHAR2 (1); v_index NUMBER; v_cnt NUMBER; v_first NUMBER; v_start NUMBER; last_date DATE; last_alj NUMBER := 0; last_duration VARCHAR2 (5); last_emplid VARCHAR2 (11); assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; attorney_data attorney_tab; crlf VARCHAR2 (2) := CHR (13) || CHR (10); PROCEDURE load_attorney_table (schedule_date IN DATE) IS CURSOR attorney_cursor IS SELECT profile_emplid, primary_hear_loc_code, NVL (secondary_hear_loc_code, 'NONE'), NVL (tertiary_hear_loc_code, 'NONE'), load_percent, period_case_load (profile_emplid, schedule_date) case_load, AE.LAST_NAME FROM attorney_profile ap, adp_employee ae WHERE ( attorney_type_code = 'TRIAL' OR attorney_type_code = 'FED') AND AP.PROFILE_EMPLID = AE.EMPLID AND load_percent > 0 AND profile_emplid <> 'OUTSIDECNCL' ORDER BY 6, 5 DESC, 2; BEGIN OPEN attorney_cursor; v_index := 1; LOOP FETCH attorney_cursor INTO attorney_data (v_index).emplid, attorney_data (v_index).primary_loc, attorney_data (v_index).secondary_loc, attorney_data (v_index).tertiary_loc, attorney_data (v_index).load_percent, attorney_data (v_index).case_load, attorney_data (v_index).attorney_last_name; EXIT WHEN attorney_cursor%NOTFOUND; v_index := v_index + 1; END LOOP; CLOSE attorney_cursor; v_cnt := attorney_data.COUNT; v_index := 1; v_start := 1; END load_attorney_table; BEGIN OPEN assigns_cursor FOR SELECT /*+ FIRST_ROWS */ l.legal_case_seq_num, s.schedule_date, s.hearing_location_seq_num, s.alj_person_seq_num, s.duration_code, s.schedule_seq_num, h.hearing_location_code, 'None' AS primary_emplid, 'None' AS secondary_emplid, 'None' AS tertiary_emplid, adjuster_flag, alj_flag, rotation_flag, calendar_flag, employer_flag, weekly_case_load, location_flag, WCB_Num FROM legal_case l, schedule s, hearing_location h, CLM_DBA.temp_data_person_address clm WHERE l.legal_case_seq_num = s.legal_case_seq_num AND l.CLAIM_ID = clm.CLM_ID AND clm.run_id = in_run_id AND l.case_status_code = 'PENDA' AND s.schedule_type_code = 'HEAR' AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') AND asgn_attorney_emplid IS NULL AND asgn_other_firm_seq_num IS NULL AND s.hearing_location_seq_num = h.hearing_location_seq_num AND l.request_type_code = 'WCB' AND ( NVL (clm.wrkr_conflict_flag, 'N') = 'N' OR clm.wrkr_conflict_flag = ' ') AND (NVL (clm.is_critical, 'N') = 'N' OR clm.is_critical = ' ') AND NOT EXISTS (SELECT 1 FROM request_issue r, request_issue_code c WHERE r.legal_case_seq_num = l.legal_case_seq_num AND r.request_issue_code = c.code AND c.recon_issue_flag = 'Y') ORDER BY s.schedule_date, s.alj_person_seq_num, s.duration_code; LOOP FETCH assigns_cursor INTO assign_rec; EXIT WHEN assigns_cursor%NOTFOUND; v_first := 0; v_assigned := 'N'; IF last_alj = assign_rec.alj_person_seq_num AND last_date = assign_rec.schedule_date AND last_duration = assign_rec.duration_code THEN assign_rec.weekly_case_load := 0; v_case_count := period_case_count (attorney_data (v_index).emplid, assign_rec.schedule_date); v_assigned := assignItBalanced ('SAME', last_emplid, 100, assign_rec); v_email_text := v_email_text || 'SAME - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseLoad: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || v_case_count || ' / ' || (attorney_data (v_index).case_load) || crlf; -- DBMS_OUTPUT.PUT_LINE('SAME - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load)); ELSE load_attorney_table (assign_rec.schedule_date); -- don't understand why the rotation flag is getting set to 'N' force it to 'Y' assign_rec.rotation_flag := 'Y'; FOR v_index IN v_start .. v_cnt LOOP IF attorney_data (v_index).primary_loc = assign_rec.hearing_location_code THEN --DBMS_OUTPUT.PUT_LINE(' v_start ' || v_start); --DBMS_OUTPUT.PUT_LINE(' v_index ' || v_index); --DBMS_OUTPUT.PUT_LINE(' emplid ' || attorney_data (v_index).emplid); v_case_count := period_case_count (attorney_data (v_index).emplid, assign_rec.schedule_date); v_assigned := assignItBalanced ('ASGN', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN v_email_text := v_email_text || 'PRIMARY 1 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || v_case_count || ' / ' || attorney_data (v_index).case_load || crlf; -- DBMS_OUTPUT.PUT_LINE('PRIMARY 1 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load)); v_start := v_index + 1; last_emplid := attorney_data (v_index).emplid; IF v_start > v_cnt THEN v_start := 1; END IF; EXIT; END IF; END IF; END LOOP; IF v_assigned = 'N' AND v_start > 1 THEN FOR v_index IN 1 .. v_start - 1 LOOP IF attorney_data (v_index).primary_loc = assign_rec.hearing_location_code THEN v_case_count := period_case_count (attorney_data (v_index).emplid, assign_rec.schedule_date); v_assigned := assignItBalanced ( 'ASGN', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN v_email_text := v_email_text || 'PRIMARY 2 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || v_case_count || ' / ' || attorney_data (v_index).case_load || crlf; -- DBMS_OUTPUT.PUT_LINE('PRIMARY 2 - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load)); v_start := v_index + 1; last_emplid := attorney_data (v_index).emplid; IF v_start > v_cnt THEN v_start := 1; END IF; EXIT; END IF; END IF; END LOOP; END IF; IF v_assigned = 'N' THEN FOR v_index IN 1 .. v_cnt LOOP IF attorney_data (v_index).secondary_loc = assign_rec.hearing_location_code THEN v_case_count := period_case_count (attorney_data (v_index).emplid, assign_rec.schedule_date); v_assigned := assignItBalanced ( 'ASGN', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_assigned = 'Y' THEN v_email_text := v_email_text || 'SECONDARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || v_case_count || ' / ' || attorney_data (v_index).case_load || crlf; -- DBMS_OUTPUT.PUT_LINE('SECONDARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).secondary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load)); last_emplid := attorney_data (v_index).emplid; EXIT; END IF; END IF; END LOOP; END IF; IF v_assigned = 'N' THEN FOR v_index IN 1 .. v_cnt LOOP IF attorney_data (v_index).tertiary_loc = assign_rec.hearing_location_code THEN v_case_count := period_case_count (attorney_data (v_index).emplid, assign_rec.schedule_date); v_assigned := assignItBalanced ( 'ASGN', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_assigned = 'Y' THEN v_email_text := v_email_text || 'TERTIARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseCount / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).primary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || v_case_count || ' / ' || attorney_data (v_index).case_load || crlf; -- DBMS_OUTPUT.PUT_LINE('TERTIARY - assigned to emplid / attorney / loc / wcb_num / hearingDate / starting caseload: ' || attorney_data (v_index).emplid || ' / ' || attorney_data (v_index).attorney_last_name || ' / ' || attorney_data (v_index).tertiary_loc || ' / ' || assign_rec.wcb_num || ' / ' || assign_rec.schedule_date || ' / ' || (attorney_data (v_index).case_load)); last_emplid := attorney_data (v_index).emplid; EXIT; END IF; END IF; END LOOP; END IF; IF v_assigned = 'Y' THEN last_date := assign_rec.schedule_date; last_alj := assign_rec.alj_person_seq_num; last_duration := assign_rec.duration_code; ELSE insert_results (assign_rec.legal_case_seq_num, 'ASGN', assign_rec.schedule_seq_num, 'NALOC', '0'); END IF; END IF; END LOOP; --Send_Assignment_Info(v_email_text); CLOSE assigns_cursor; END assign_remaining_cases; PROCEDURE assign_rotation_cases (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2) IS assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; v_assigned VARCHAR2 (1); BEGIN OPEN assigns_cursor FOR SELECT /*+ FIRST_ROWS */ l.legal_case_seq_num, s.schedule_date, s.hearing_location_seq_num, s.alj_person_seq_num, s.duration_code, s.schedule_seq_num, h.hearing_location_code, o.attorney_emplid, 'None' AS secondary_emplid, 'None' AS tertiary_emplid, adjuster_flag, alj_flag, rotation_flag, calendar_flag, employer_flag, weekly_case_load, location_flag, wcb_num FROM legal_case l, schedule s, hearing_location h, out_of_town_rotation_assgn o WHERE l.legal_case_seq_num = s.legal_case_seq_num AND l.case_status_code = 'PENDA' AND s.schedule_type_code = 'HEAR' AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') AND asgn_attorney_emplid IS NULL AND asgn_other_firm_seq_num IS NULL AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = o.hearing_location_code AND s.schedule_date BETWEEN o.eff_date AND o.exp_date AND NOT EXISTS (SELECT 1 FROM request_issue r, request_issue_code rc WHERE s.legal_case_seq_num = r.legal_case_seq_num AND r.request_issue_code = rc.code AND rc.recon_issue_flag = 'Y') ORDER BY schedule_date; LOOP FETCH assigns_cursor INTO assign_rec; EXIT WHEN assigns_cursor%NOTFOUND; assign_rec.rotation_flag := 'N'; v_assigned := assignIt ('ROT', assign_rec.primary_emplid, 100, assign_rec); END LOOP; CLOSE assigns_cursor; END assign_rotation_cases; PROCEDURE assign_same (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_emplid VARCHAR2 (11); assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; CURSOR get_same_loc_date IS SELECT /*+ FIRST_ROWS */ l.legal_case_seq_num, schedule_date, hearing_location_seq_num, alj_person_seq_num, duration_code, schedule_seq_num, NVL (c.is_critical, 'N') is_critical, l.request_type_code, DECODE (c.wrkr_conflict_flag, ' ', 'N', c.wrkr_conflict_flag) wrkr_conflict FROM legal_case l, schedule s, CLM_DBA.temp_data_person_address c WHERE l.legal_case_seq_num = s.legal_case_seq_num AND l.claim_id = c.clm_id AND c.run_id = in_run_id AND l.case_status_code = 'PENDA' AND s.schedule_type_code = 'HEAR' AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') AND asgn_attorney_emplid IS NULL AND asgn_other_firm_seq_num IS NULL AND NOT EXISTS (SELECT 1 FROM request_issue r, request_issue_code rc WHERE s.legal_case_seq_num = r.legal_case_seq_num AND r.request_issue_code = rc.code AND rc.recon_issue_flag = 'Y'); sameLoc_rec get_same_loc_date%ROWTYPE; BEGIN FOR sameLoc_rec IN get_same_loc_date LOOP v_emplid := same_time_loc_check (sameLoc_rec.schedule_date, sameLoc_rec.duration_code, sameLoc_rec.hearing_location_seq_num, sameLoc_rec.alj_person_seq_num, sameLoc_rec.is_critical, sameLoc_rec.request_type_code, sameLoc_rec.wrkr_conflict); IF v_emplid <> 'NONE' THEN assign_rec.legal_case_seq_num := sameLoc_rec.legal_case_seq_num; assign_rec.schedule_date := sameLoc_rec.schedule_date; assign_rec.schedule_seq_num := sameLoc_rec.schedule_seq_num; assign_rec.hearing_location_seq_num := sameLoc_rec.hearing_location_seq_num; assign_rec.alj_person_seq_num := sameLoc_rec.alj_person_seq_num; assign_rec.duration_code := sameLoc_rec.duration_code; assign_rec.hearing_location_code := 'NONE'; assign_rec.adjuster_flag := adjuster_flag; assign_rec.alj_flag := alj_flag; assign_rec.rotation_flag := rotation_flag; assign_rec.calendar_flag := calendar_flag; assign_rec.employer_flag := employer_flag; assign_rec.weekly_case_load := weekly_case_load; assign_rec.location_flag := location_flag; v_assigned := assignIt ('SAME', v_emplid, 100, assign_rec); END IF; END LOOP; END assign_same; PROCEDURE assign_wrkr_conflict_cases (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2, in_run_id IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_index NUMBER; v_cnt NUMBER; v_first NUMBER; attorney_data attorney_tab; assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; PROCEDURE load_attorney_table (schedule_date IN DATE) IS CURSOR attorney_cursor IS SELECT profile_emplid, primary_hear_loc_code, NVL (secondary_hear_loc_code, 'NONE'), NVL (tertiary_hear_loc_code, 'NONE'), load_percent, period_case_load (profile_emplid, schedule_date) FROM attorney_profile WHERE confidential_claims_flag = 'Y' AND ( attorney_type_code = 'TRIAL' OR attorney_type_code = 'FED') AND load_percent > 0 ORDER BY 5, 2; BEGIN OPEN attorney_cursor; v_index := 1; LOOP FETCH attorney_cursor INTO attorney_data (v_index).emplid, attorney_data (v_index).primary_loc, attorney_data (v_index).secondary_loc, attorney_data (v_index).tertiary_loc, attorney_data (v_index).load_percent, attorney_data (v_index).case_load; EXIT WHEN attorney_cursor%NOTFOUND; v_index := v_index + 1; END LOOP; v_cnt := attorney_data.COUNT; v_index := 1; v_first := 0; CLOSE attorney_cursor; END load_attorney_table; BEGIN OPEN assigns_cursor FOR SELECT /*+ FIRST_ROWS */ l.legal_case_seq_num, s.schedule_date, s.hearing_location_seq_num, s.alj_person_seq_num, s.duration_code, s.schedule_seq_num, h.hearing_location_code, 'None' AS primary_emplid, 'None' AS secondary_emplid, 'None' AS tertiary_emplid, adjuster_flag, alj_flag, rotation_flag, calendar_flag, employer_flag, weekly_case_load, location_flag, wcb_num FROM legal_case l, schedule s, hearing_location h, clm_dba.temp_data_person_address clm WHERE l.legal_case_seq_num = s.legal_case_seq_num AND l.request_type_code = 'WCB' AND l.claim_id = clm.clm_id AND clm.run_id = in_run_id AND NVL (clm.wrkr_conflict_flag, 'N') = 'Y' AND l.case_status_code = 'PENDA' AND s.schedule_type_code = 'HEAR' AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') AND asgn_attorney_emplid IS NULL AND asgn_other_firm_seq_num IS NULL AND s.hearing_location_seq_num = h.hearing_location_seq_num AND NOT EXISTS (SELECT 1 FROM request_issue r, request_issue_code c WHERE r.legal_case_seq_num = l.legal_case_seq_num AND r.request_issue_code = c.code AND c.recon_issue_flag = 'Y'); LOOP FETCH assigns_cursor INTO assign_rec; EXIT WHEN assigns_cursor%NOTFOUND; v_assigned := 'N'; load_attorney_table (assign_rec.schedule_date); FOR v_index IN 1 .. v_cnt LOOP IF attorney_data (v_index).primary_loc = assign_rec.hearing_location_code THEN v_assigned := assignIt ('WRKC', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN EXIT; END IF; END IF; END LOOP; IF v_assigned = 'N' THEN FOR v_index IN 1 .. v_cnt LOOP IF attorney_data (v_index).secondary_loc = assign_rec.hearing_location_code THEN v_assigned := assignIt ('WRKC', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN EXIT; END IF; END IF; END LOOP; END IF; IF v_assigned = 'N' THEN FOR v_index IN 1 .. v_cnt LOOP IF attorney_data (v_index).tertiary_loc = assign_rec.hearing_location_code THEN v_assigned := assignIt ('WRKC', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_first = 0 THEN v_first := v_index; END IF; IF v_assigned = 'Y' THEN EXIT; END IF; END IF; END LOOP; END IF; IF v_assigned = 'N' THEN assign_rec.weekly_case_load := 0; assign_rec.location_flag := 'N'; assign_rec.calendar_flag := 'N'; IF v_first > 0 THEN v_assigned := assignIt ('WRKC', attorney_data (v_first).emplid, attorney_data (v_first).load_percent, assign_rec); END IF; IF v_assigned = 'N' THEN FOR v_index IN 1 .. v_cnt LOOP v_assigned := assignIt ('WRKC', attorney_data (v_index).emplid, attorney_data (v_index).load_percent, assign_rec); IF v_assigned = 'Y' THEN EXIT; END IF; END LOOP; END IF; END IF; END LOOP; CLOSE assigns_cursor; END assign_wrkr_conflict_cases; PROCEDURE previously_assigned (adjuster_flag IN VARCHAR2, alj_flag IN VARCHAR2, rotation_flag IN VARCHAR2, calendar_flag IN VARCHAR2, employer_flag IN VARCHAR2, weekly_case_load IN NUMBER, location_flag IN VARCHAR2) IS v_assigned VARCHAR2 (1); v_last_case NUMBER := 0; assigns_cursor assign_cursor; assign_rec assigns_cursor%ROWTYPE; no_longer_active EXCEPTION; PRAGMA EXCEPTION_INIT (no_longer_active, -20001); BEGIN RAISE no_longer_active; END previously_assigned; FUNCTION location_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_duration_code IN VARCHAR2, in_hear_loc IN NUMBER) RETURN NUMBER AS v_conflict_count NUMBER; BEGIN BEGIN SELECT COUNT (*) INTO v_conflict_count FROM schedule s WHERE schedule_status_code IN ('SET', 'RESET') AND asgn_attorney_emplid = in_Emplid AND in_date = schedule_date AND NVL (hearing_location_seq_num, 0) <> in_hear_Loc; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; RETURN v_conflict_count; END location_conflict_check; FUNCTION calendar_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_duration_code IN VARCHAR2, in_hear_loc IN NUMBER, in_alj IN NUMBER) RETURN NUMBER AS v_conflict_count NUMBER; BEGIN IF in_duration_code = 'AM' OR in_duration_code = 'PM' THEN BEGIN SELECT COUNT (*) INTO v_conflict_count FROM schedule s, duration_code d WHERE schedule_status_code IN ('SET', 'RESET') AND asgn_attorney_emplid = in_Emplid AND in_date BETWEEN schedule_date AND schedule_date + DECODE (days_num, 0, 0, days_num - 1) AND (s.duration_code = in_Duration_code OR days_num > 0) AND ( NVL (alj_person_seq_num, 0) <> in_alj OR NVL (hearing_location_seq_num, 0) <> in_hear_Loc) AND s.duration_code = d.code; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; ELSE BEGIN SELECT COUNT (*) INTO v_conflict_count FROM schedule s, duration_code d WHERE schedule_status_code IN ('SET', 'RESET') AND asgn_attorney_emplid = in_Emplid AND in_date BETWEEN schedule_date AND schedule_date + DECODE (days_num, 0, 0, days_num - 1) AND ( NVL (alj_person_seq_num, 0) <> in_alj OR NVL (hearing_location_seq_num, 0) <> in_hear_Loc) AND s.duration_code = d.code; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; END IF; IF v_conflict_count IS NULL THEN v_conflict_count := 0; END IF; RETURN v_conflict_count; END calendar_conflict_check; FUNCTION vacation_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_duration_code IN VARCHAR2, in_code IN VARCHAR2) RETURN NUMBER AS v_vacation_count NUMBER; v_out_count NUMBER; BEGIN IF in_code = 'Y' THEN BEGIN -- leave more than 4 days don't assign 3 before and 1 after SELECT COUNT (*) INTO v_vacation_count FROM attorney_schedule WHERE schedule_emplid = in_emplid AND ( ( attorney_schedule_type_code = 'LEAVE' AND exp_date >= eff_date + 3 AND TRUNC (SYSDATE) BETWEEN ( NEXT_DAY ( eff_date - 1, 'Friday') - 4) + DECODE ( eff_date - ( NEXT_DAY ( eff_date - 1, 'Friday') - 4), 0, -4, 0) + DECODE ( eff_date - ( NEXT_DAY ( eff_date - 1, 'Friday') - 4), 1, -4, 0) + DECODE ( eff_date - ( NEXT_DAY ( eff_date - 1, 'Friday') - 4), 2, -4, 0) + DECODE ( eff_date - ( NEXT_DAY ( eff_date - 1, 'Friday') - 4), 3, 0, 0) + DECODE ( eff_date - ( NEXT_DAY ( eff_date - 1, 'Friday') - 4), 4, +1, 0) AND exp_date + 1 + DECODE ( NEXT_DAY ( exp_date - 1, 'Friday') - exp_date, 0, 2, 0))); EXCEPTION WHEN NO_DATA_FOUND THEN v_vacation_count := 0; END; END IF; IF v_vacation_count IS NULL THEN v_vacation_count := 0; END IF; IF in_duration_code = 'AM' OR in_duration_code = 'PM' THEN BEGIN SELECT COUNT (*) INTO v_out_count FROM attorney_schedule s, duration_code d WHERE s.schedule_emplid = in_emplid AND in_date BETWEEN s.eff_date AND s.exp_date AND (in_duration_code = s.duration_code OR d.days_num > 0) AND s.duration_code = d.code; EXCEPTION WHEN NO_DATA_FOUND THEN v_out_count := 0; END; ELSE BEGIN SELECT COUNT (*) INTO v_out_count FROM attorney_schedule s, duration_code d WHERE s.schedule_emplid = in_emplid AND in_date BETWEEN s.eff_date AND s.exp_date -- AND (in_duration_code = s.duration_code OR d.days_num > 0) AND s.duration_code = d.code; EXCEPTION WHEN NO_DATA_FOUND THEN v_out_count := 0; END; END IF; IF v_out_count IS NULL THEN v_out_count := 0; END IF; RETURN v_vacation_count + v_out_count; END vacation_conflict_check; FUNCTION case_load (in_emplid IN VARCHAR2) RETURN NUMBER AS v_case_load NUMBER; BEGIN BEGIN SELECT ROUND (SUM (case_weight_value * (100 / load_percent)), 0) INTO v_case_load FROM legal_case l, schedule s, attorney_profile a WHERE l.legal_case_seq_num = s.legal_case_seq_num AND case_status_code <> 'CLOSE' AND s.schedule_seq_num = (SELECT MAX (schedule_seq_num) FROM schedule WHERE legal_case_seq_num = l.legal_case_seq_num AND ( schedule_type_code = 'HEAR' OR schedule_type_code = 'MED')) AND s.asgn_attorney_emplid = profile_emplid AND asgn_attorney_emplid = in_emplid; EXCEPTION WHEN ZERO_DIVIDE THEN v_case_load := 0; WHEN NO_DATA_FOUND THEN v_case_load := 0; END; IF v_case_load IS NULL THEN v_case_load := 0; END IF; RETURN v_case_load; END case_load; FUNCTION period_case_load (in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER AS v_case_load NUMBER; v_vac_days NUMBER; v_total NUMBER; v_rot_default NUMBER; v_eff DATE; v_rot_count NUMBER; BEGIN BEGIN SELECT ROUND (SUM (case_weight_value * (100 / load_percent)), 0) INTO v_case_load FROM legal_case l, schedule s, attorney_profile a WHERE l.legal_case_seq_num = s.legal_case_seq_num AND case_status_code <> 'CLOSE' AND ( schedule_type_code = 'HEAR' OR schedule_type_code = 'MED') AND s.asgn_attorney_emplid = profile_emplid AND asgn_attorney_emplid = in_emplid AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - 12) AND ( NEXT_DAY (hearingDate - 1, 'Friday') + 7); EXCEPTION WHEN ZERO_DIVIDE THEN v_case_load := 0; WHEN NO_DATA_FOUND THEN v_case_load := 0; END; IF v_case_load IS NULL THEN v_case_load := 0; END IF; BEGIN v_rot_default := 0; SELECT MIN (Rotation_average_case_count), MIN (r.eff_date) INTO v_rot_default, v_eff FROM hearing_location h, out_of_town_rotation_assgn r WHERE h.hearing_location_code = r.hearing_location_code AND r.attorney_emplid = in_emplid AND r.eff_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - 12) AND ( NEXT_DAY (hearingDate - 1, 'Friday') + 7); EXCEPTION WHEN NO_DATA_FOUND THEN v_rot_default := 0; END; IF v_rot_default > 0 THEN v_rot_count := get_weekly_case_load (in_emplid, v_eff); IF v_rot_count < v_rot_default THEN v_case_load := v_case_load - v_rot_count + v_rot_default; END IF; END IF; v_vac_days := get_vacation_days (in_emplid, hearingDate, 12, 7); v_total := v_case_load + v_vac_days; RETURN v_total; END period_case_load; FUNCTION period_case_count (in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER AS v_total NUMBER; BEGIN BEGIN SELECT COUNT (*) INTO v_total FROM legal_case l, schedule s, attorney_profile a WHERE l.legal_case_seq_num = s.legal_case_seq_num AND case_status_code <> 'CLOSE' AND ( schedule_type_code = 'HEAR' OR schedule_type_code = 'MED') AND s.asgn_attorney_emplid = profile_emplid AND asgn_attorney_emplid = in_emplid AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - 12) AND ( NEXT_DAY (hearingDate - 1, 'Friday') + 7); END; RETURN v_total; END period_case_count; FUNCTION get_weekly_case_load (in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER AS v_case_load NUMBER; BEGIN BEGIN SELECT ROUND (SUM (case_weight_value * (100 / load_percent)), 0) INTO v_case_load FROM legal_case l, schedule s, attorney_profile a WHERE l.legal_case_seq_num = s.legal_case_seq_num AND case_status_code <> 'CLOSE' AND ( schedule_type_code = 'HEAR' OR schedule_type_code = 'MED') AND s.asgn_attorney_emplid = profile_emplid AND asgn_attorney_emplid = in_emplid AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - 5) AND NEXT_DAY (hearingDate - 1, 'Friday'); EXCEPTION WHEN ZERO_DIVIDE THEN v_case_load := 0; WHEN NO_DATA_FOUND THEN v_case_load := 0; END; IF v_case_load IS NULL THEN v_case_load := 0; END IF; RETURN v_case_load; END get_weekly_case_load; FUNCTION get_hearing_days (in_emplid IN VARCHAR2, hearingDate IN DATE) RETURN NUMBER AS v_case_load NUMBER; v_total NUMBER; BEGIN BEGIN SELECT COUNT (DISTINCT (schedule_date)) INTO v_case_load FROM legal_case l, schedule s, attorney_profile a WHERE l.legal_case_seq_num = s.legal_case_seq_num AND case_status_code <> 'CLOSE' AND ( schedule_type_code = 'HEAR' OR schedule_type_code = 'MED') AND s.asgn_attorney_emplid = profile_emplid AND asgn_attorney_emplid = in_emplid AND schedule_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - 5) AND NEXT_DAY (hearingDate - 1, 'Friday'); -- AND schedule_date <> hearingDate; changed 10/04/2011 EXCEPTION WHEN ZERO_DIVIDE THEN v_case_load := 0; WHEN NO_DATA_FOUND THEN v_case_load := 0; END; IF v_case_load IS NULL THEN v_case_load := 0; END IF; v_total := v_case_load + get_vacation_days (in_emplid, hearingDate, 5, 0); RETURN v_total; END get_hearing_days; FUNCTION get_vacation_days (in_emplid IN VARCHAR2, hearingDate IN DATE, in_before IN NUMBER, in_after IN NUMBER) RETURN NUMBER AS v_vac_days1 NUMBER; v_vac_days2 NUMBER; v_vac_days3 NUMBER; v_vac_days4 NUMBER; v_total NUMBER; BEGIN BEGIN SELECT SUM (Sp72219_Day_Count (eff_date, exp_date) + 1) INTO v_vac_days1 FROM attorney_schedule a WHERE schedule_emplid = in_emplid AND ( attorney_schedule_type_code = 'VAC' OR attorney_schedule_type_code = 'LEAVE') AND eff_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - in_before) AND NEXT_DAY (hearingDate - 1, 'Friday') + in_after AND exp_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - in_before) AND NEXT_DAY (hearingDate - 1, 'Friday') + in_after; EXCEPTION WHEN NO_DATA_FOUND THEN v_vac_days1 := 0; END; BEGIN SELECT SUM ( Sp72219_Day_Count ( (NEXT_DAY (hearingDate - 1, 'Friday') - in_before), exp_date) + 1) INTO v_vac_days2 FROM attorney_schedule a WHERE schedule_emplid = in_emplid AND ( attorney_schedule_type_code = 'VAC' OR attorney_schedule_type_code = 'LEAVE') AND eff_date < (NEXT_DAY (hearingDate - 1, 'Friday') - in_before) AND exp_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - in_before) AND NEXT_DAY (hearingDate - 1, 'Friday') + in_after; EXCEPTION WHEN NO_DATA_FOUND THEN v_vac_days2 := 0; END; BEGIN SELECT SUM ( Sp72219_Day_Count ( eff_date, NEXT_DAY (hearingDate - 1, 'Friday') + in_after) + 1) INTO v_vac_days3 FROM attorney_schedule a WHERE schedule_emplid = in_emplid AND ( attorney_schedule_type_code = 'VAC' OR attorney_schedule_type_code = 'LEAVE') AND eff_date BETWEEN ( NEXT_DAY (hearingDate - 1, 'Friday') - In_before) AND NEXT_DAY (hearingDate - 1, 'Friday') + in_after AND exp_date > NEXT_DAY (hearingDate - 1, 'Friday') + in_after; EXCEPTION WHEN NO_DATA_FOUND THEN v_vac_days3 := 0; END; BEGIN SELECT SUM ( Sp72219_Day_Count ( (NEXT_DAY (hearingDate - 1, 'Friday') - in_before), NEXT_DAY (hearingDate - 1, 'Friday') + in_after) + 1) INTO v_vac_days4 FROM attorney_schedule a WHERE schedule_emplid = in_emplid AND ( attorney_schedule_type_code = 'VAC' OR attorney_schedule_type_code = 'LEAVE') AND eff_date < (NEXT_DAY (hearingDate - 1, 'Friday') - in_before) AND exp_date > NEXT_DAY (hearingDate - 1, 'Friday') + in_after; EXCEPTION WHEN NO_DATA_FOUND THEN v_vac_days4 := 0; END; IF v_vac_days1 IS NULL THEN v_vac_days1 := 0; END IF; IF v_vac_days2 IS NULL THEN v_vac_days2 := 0; END IF; IF v_vac_days3 IS NULL THEN v_vac_days3 := 0; END IF; IF v_vac_days4 IS NULL THEN v_vac_days4 := 0; END IF; v_total := v_vac_days1 + v_vac_days2 + v_vac_days3 + v_vac_days4; RETURN v_total; END get_vacation_days; FUNCTION assignIt (in_code IN VARCHAR2, in_emplid IN VARCHAR2, in_load_percent IN NUMBER, in_assign_rec IN assignment_rec) RETURN VARCHAR2 AS v_assign VARCHAR2 (1) := 'N'; calendar_result NUMBER := 0; adjuster_result NUMBER := 0; rotation_result NUMBER := 0; employer_result NUMBER := 0; alj_result NUMBER := 0; attorney_result NUMBER := 0; load_result NUMBER := 0; final_result NUMBER := 0; vacation_result NUMBER := 0; holiday_result NUMBER := 0; location_result NUMBER := 0; hearing_days NUMBER := 3; v_date DATE; BEGIN BEGIN v_assign := 'N'; calendar_result := 0; adjuster_result := 0; rotation_result := 0; employer_result := 0; alj_result := 0; load_result := 0; final_result := 0; vacation_result := 0; holiday_result := 0; location_result := 0; SELECT sp72353_next_work_day (in_assign_rec.schedule_Date) INTO v_date FROM DUAL; IF v_date = in_assign_rec.schedule_Date THEN holiday_result := 0; ELSE holiday_result := 1; END IF; IF in_assign_rec.weekly_case_load > 0 THEN load_result := get_hearing_days (in_emplid, in_assign_rec.schedule_Date); IF in_load_percent < 34 THEN hearing_days := 1; ELSE IF in_load_percent < 67 THEN hearing_days := 2; END IF; END IF; IF load_result < hearing_days THEN load_result := 0; END IF; END IF; IF in_assign_rec.employer_flag = 'Y' THEN employer_result := employer_conflict_check (in_assign_rec.legal_case_Seq_num, in_emplid); END IF; IF in_assign_rec.adjuster_flag = 'Y' THEN adjuster_result := adjuster_conflict_check (in_assign_rec.legal_case_Seq_num, in_emplid); END IF; IF in_assign_rec.alj_flag = 'Y' THEN alj_result := alj_conflict_check (in_assign_rec.alj_person_seq_num, in_emplid); END IF; attorney_result := attorney_conflict_check (in_assign_rec.legal_case_Seq_num, in_emplid); IF in_assign_rec.rotation_flag = 'Y' THEN rotation_result := rotation_conflict_check (in_emplid, in_assign_rec.schedule_Date, in_assign_rec.hearing_location_code); END IF; IF in_assign_rec.calendar_flag = 'Y' OR in_assign_rec.calendar_flag = 'H' THEN calendar_result := calendar_conflict_check ( in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.hearing_location_seq_num, in_assign_rec.alj_person_seq_num); vacation_result := vacation_conflict_check (in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.calendar_flag); END IF; IF in_assign_rec.calendar_flag = 'C' THEN calendar_result := calendar_conflict_check ( in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.hearing_location_seq_num, in_assign_rec.alj_person_seq_num); END IF; IF in_assign_rec.location_flag = 'Y' THEN location_result := location_conflict_check ( in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.hearing_location_seq_num); END IF; final_result := adjuster_result + alj_result + rotation_result + calendar_result + load_result + vacation_result + holiday_result + employer_result + location_result + attorney_result; IF final_result = 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'CA', IN_emplid); updateSchedule (in_assign_rec.schedule_seq_num, in_emplid, SYSDATE, SYSDATE, 'Assignment'); Pk80001_Legalnet.updatecasestatus ( in_assign_rec.legal_case_Seq_num, 'ASSGN', SYSDATE, 'Assignment'); v_assign := 'Y'; ELSE v_assign := 'N'; IF holiday_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAHOL', IN_emplid); END IF; IF employer_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAEMP', IN_emplid); END IF; IF adjuster_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAADJ', IN_emplid); END IF; IF alj_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAALJ', IN_emplid); END IF; IF attorney_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAATT', IN_emplid); END IF; IF rotation_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAROT', IN_emplid); END IF; IF calendar_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAC', IN_emplid); END IF; IF vacation_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAVAC', IN_emplid); END IF; IF load_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'LOAD', IN_emplid); END IF; IF location_result > 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NALCC', IN_emplid); END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_assign := 'N'; END; RETURN v_assign; END assignIt; FUNCTION assignItBalanced (in_code IN VARCHAR2, in_emplid IN VARCHAR2, in_load_percent IN NUMBER, in_assign_rec IN assignment_rec) RETURN VARCHAR2 AS v_assign VARCHAR2 (1) := 'N'; calendar_result NUMBER := 0; adjuster_result NUMBER := 0; rotation_result NUMBER := 0; employer_result NUMBER := 0; alj_result NUMBER := 0; attorney_result NUMBER := 0; load_result NUMBER := 0; final_result NUMBER := 0; vacation_result NUMBER := 0; holiday_result NUMBER := 0; location_result NUMBER := 0; hearing_days NUMBER := 3; v_date DATE; BEGIN BEGIN v_assign := 'N'; calendar_result := 0; adjuster_result := 0; rotation_result := 0; employer_result := 0; alj_result := 0; load_result := 0; final_result := 0; vacation_result := 0; holiday_result := 0; location_result := 0; SELECT sp72353_next_work_day (in_assign_rec.schedule_Date) INTO v_date FROM DUAL; IF v_date = in_assign_rec.schedule_Date THEN holiday_result := 0; ELSE holiday_result := 1; END IF; IF in_assign_rec.employer_flag = 'Y' THEN employer_result := employer_conflict_check (in_assign_rec.legal_case_Seq_num, in_emplid); END IF; IF in_assign_rec.adjuster_flag = 'Y' THEN adjuster_result := adjuster_conflict_check (in_assign_rec.legal_case_Seq_num, in_emplid); END IF; IF in_assign_rec.alj_flag = 'Y' THEN alj_result := alj_conflict_check (in_assign_rec.alj_person_seq_num, in_emplid); END IF; attorney_result := attorney_conflict_check (in_assign_rec.legal_case_Seq_num, in_emplid); IF in_assign_rec.rotation_flag = 'Y' THEN rotation_result := rotation_conflict_check (in_emplid, in_assign_rec.schedule_Date, in_assign_rec.hearing_location_code); END IF; IF in_assign_rec.calendar_flag = 'Y' OR in_assign_rec.calendar_flag = 'H' THEN calendar_result := calendar_conflict_check ( in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.hearing_location_seq_num, in_assign_rec.alj_person_seq_num); vacation_result := vacation_conflict_check (in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.calendar_flag); END IF; IF in_assign_rec.calendar_flag = 'C' THEN calendar_result := calendar_conflict_check ( in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.hearing_location_seq_num, in_assign_rec.alj_person_seq_num); END IF; IF in_assign_rec.location_flag = 'Y' THEN location_result := location_conflict_check ( in_emplid, in_assign_rec.schedule_Date, in_assign_rec.duration_code, in_assign_rec.hearing_location_seq_num); END IF; final_result := adjuster_result + alj_result + rotation_result + calendar_result + load_result + vacation_result + holiday_result + employer_result + location_result + attorney_result; IF final_result = 0 THEN insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'CA', IN_emplid); updateSchedule (in_assign_rec.schedule_seq_num, in_emplid, SYSDATE, SYSDATE, 'Assignment'); Pk80001_Legalnet.updatecasestatus ( in_assign_rec.legal_case_Seq_num, 'ASSGN', SYSDATE, 'Assignment'); v_assign := 'Y'; ELSE v_assign := 'N'; IF holiday_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAHOL'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAHOL', IN_emplid); END IF; IF employer_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAEMP'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAEMP', IN_emplid); END IF; IF adjuster_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAADJ'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAADJ', IN_emplid); END IF; IF alj_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAALJ'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAALJ', IN_emplid); END IF; IF attorney_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAATT'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAATT', IN_emplid); END IF; IF rotation_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAROT'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAROT', IN_emplid); END IF; IF calendar_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAC'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAC', IN_emplid); END IF; IF vacation_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NAVAC'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NAVAC', IN_emplid); END IF; IF load_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'LOAD'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'LOAD', IN_emplid); END IF; IF location_result > 0 THEN DBMS_OUTPUT.PUT_LINE ( ' result emplid / resultCode: ' || in_emplid || ' / ' || 'NALCC'); insert_results (in_assign_rec.legal_case_Seq_num, in_code, in_assign_rec.schedule_seq_num, 'NALCC', IN_emplid); END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_assign := 'N'; END; RETURN v_assign; END assignItBalanced; PROCEDURE get_results (in_num IN NUMBER, in_type IN VARCHAR2, in_date IN DATE, recordset OUT assign_results_cursor) AS BEGIN IF in_type = 'ASSIGN' THEN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, rul.code_descr AS rule_descr, res.code_descr AS result_descr, r.assignment_attorney_emplid, NVL ( a.LAST_NAME || ', ' || DECODE (a.preferred_name, ' ', a.FIRST_NAME, a.preferred_name), 'Not Assigned') AS asgn_attorney_name FROM adp_employee a, assignment_result r, assignment_rule_code rul, assignment_result_code res WHERE a.emplid(+) = r.assignment_attorney_emplid AND r.assignment_rule_code = rul.code AND r.assignment_result_code = res.code AND r.assignment_result_seq_num > in_num ORDER BY r.legal_case_seq_num, r.assignment_result_seq_num; END IF; IF in_type = 'DATE' THEN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, rul.code_descr AS rule_descr, res.code_descr AS result_descr, r.assignment_attorney_emplid, NVL ( a.LAST_NAME || ', ' || DECODE (a.preferred_name, ' ', a.FIRST_NAME, a.preferred_name), 'Not Assigned') AS asgn_attorney_name FROM adp_employee a, assignment_result r, assignment_rule_code rul, assignment_result_code res WHERE a.emplid(+) = r.assignment_attorney_emplid AND r.assignment_rule_code = rul.code AND r.assignment_result_code = res.code AND TRUNC (r.create_date) = in_date ORDER BY r.legal_case_seq_num, r.assignment_result_seq_num; END IF; IF in_type = 'CASE' THEN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, rul.code_descr AS rule_descr, res.code_descr AS result_descr, r.assignment_attorney_emplid, NVL ( a.LAST_NAME || ', ' || DECODE (a.preferred_name, ' ', a.FIRST_NAME, a.preferred_name), 'Not Assigned') AS asgn_attorney_name FROM adp_employee a, assignment_result r, assignment_rule_code rul, assignment_result_code res WHERE a.emplid(+) = r.assignment_attorney_emplid AND r.assignment_rule_code = rul.code AND r.assignment_result_code = res.code AND r.legal_case_seq_num = in_num ORDER BY r.legal_case_seq_num, r.assignment_result_seq_num; END IF; END get_results; PROCEDURE get_assign_results (recordset OUT assign_header_cursor, in_num IN NUMBER, in_type IN VARCHAR2, in_date IN DATE) AS BEGIN IF in_type = 'ASSIGN' THEN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, s.asgn_attorney_emplid, a.LAST_NAME || ', ' || DECODE (a.preferred_name, ' ', a.FIRST_NAME, a.preferred_name) AS asgn_attorney_name, s.schedule_date AS hearing_date, r.create_date, l.code_descr AS hearing_location, r.assignment_result_code AS result_code, a.email_address, TRIM (p.first_name || ' ' || p.last_name) AS alj_name, s.schedule_time, cas.claim_id FROM adp_employee a, assignment_result r, legal_case cas, schedule s, hearing_location h, hearing_location_code l, legal_person p WHERE r.schedule_seq_num = s.schedule_seq_num AND a.emplid(+) = r.assignment_attorney_emplid AND r.assignment_result_seq_num > in_num AND r.assignment_result_Seq_num = (SELECT MAX (assignment_result_seq_num) FROM assignment_result WHERE legal_case_seq_num = r.legal_case_seq_num) AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND r.legal_case_seq_num = cas.legal_case_seq_num AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND r.assignment_result_code IN ('CA', 'CAC') UNION SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, s.asgn_attorney_emplid, 'Not Assigned' AS asgn_attorney_name, s.schedule_date AS hearing_date, r.create_date, l.code_descr AS hearing_location, r.assignment_result_code AS result_code, 'NONE' AS email_address, TRIM (p.first_name || ' ' || p.last_name) AS alj_name, s.schedule_time, cas.claim_id FROM assignment_result r, legal_case cas, schedule s, hearing_location h, hearing_location_code l, legal_person p WHERE r.schedule_seq_num = s.schedule_seq_num AND r.assignment_result_seq_num > in_num AND r.assignment_result_Seq_num = (SELECT MAX (assignment_result_seq_num) FROM assignment_result WHERE legal_case_seq_num = r.legal_case_seq_num) AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND r.legal_case_seq_num = cas.legal_case_seq_num AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND r.assignment_result_code NOT IN ('CA', 'CAC') ORDER BY 3, 5; END IF; IF in_type = 'DATE' THEN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, s.asgn_attorney_emplid, a.LAST_NAME || ', ' || DECODE (a.preferred_name, ' ', a.FIRST_NAME, a.preferred_name) AS asgn_attorney_name, s.schedule_date AS hearing_date, r.create_date, l.code_descr AS hearing_location, r.assignment_result_code AS result_code, --'NOTUSED' AS adjusterMailId, a.email_address, TRIM (p.first_name || ' ' || p.last_name) AS alj_name, schedule_time, cas.claim_id FROM adp_employee a, assignment_result r, schedule s, hearing_location h, hearing_location_code l, legal_case cas, legal_person p WHERE r.schedule_seq_num = s.schedule_seq_num AND a.emplid(+) = r.assignment_attorney_emplid AND TRUNC (r.create_date) = in_date AND r.assignment_result_Seq_num = (SELECT MAX (assignment_result_seq_num) FROM assignment_result WHERE legal_case_seq_num = r.legal_case_seq_num AND TRUNC (create_date) = in_date) AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND r.legal_case_seq_num = cas.legal_case_seq_num AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND r.assignment_result_code IN ('CA', 'CAC') UNION SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, s.asgn_attorney_emplid, 'Not Assigned' AS asgn_attorney_name, s.schedule_date AS hearing_date, r.create_date, l.code_descr AS hearing_location, r.assignment_result_code AS result_code, --'NOTUSED', 'NONE' AS email_address, TRIM (p.first_name || ' ' || p.last_name) AS alj_name, schedule_time, cas.claim_id FROM assignment_result r, schedule s, hearing_location h, hearing_location_code l, legal_case cas, legal_person p WHERE r.schedule_seq_num = s.schedule_seq_num AND TRUNC (r.create_date) = in_date AND r.assignment_result_Seq_num = (SELECT MAX (assignment_result_seq_num) FROM assignment_result WHERE legal_case_seq_num = r.legal_case_seq_num AND TRUNC (create_date) = in_date) AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND r.legal_case_seq_num = cas.legal_case_seq_num AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND r.assignment_result_code NOT IN ('CA', 'CAC') ORDER BY 3, 5; END IF; IF in_type = 'CASE' THEN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, s.asgn_attorney_emplid, a.LAST_NAME || ', ' || DECODE (a.preferred_name, ' ', a.FIRST_NAME, a.preferred_name) AS asgn_attorney_name, s.schedule_date AS hearing_date, r.create_date, l.code_descr AS hearing_location, r.assignment_result_code AS result_code, --'NOTUSED', a.email_address, TRIM (p.first_name || ' ' || p.last_name) AS alj_name, schedule_time, cas.claim_id FROM adp_employee a, assignment_result r, schedule s, hearing_location h, hearing_location_code l, legal_case cas, legal_person p WHERE r.schedule_seq_num = s.schedule_seq_num AND a.emplid(+) = r.assignment_attorney_emplid AND r.legal_case_seq_num = in_num AND r.assignment_result_Seq_num = (SELECT MAX (assignment_result_seq_num) FROM assignment_result WHERE legal_case_seq_num = r.legal_case_seq_num) AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND r.legal_case_seq_num = cas.legal_case_seq_num AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND r.assignment_result_code IN ('CA', 'CAC') UNION SELECT /*+ FIRST_ROWS */ r.assignment_result_seq_num, r.LEGAL_CASE_seq_num, r.schedule_seq_num, Pk80001_Legalnet.get_case_num (r.legal_case_seq_num) AS case_num, s.asgn_attorney_emplid, 'Not Assigned' AS asgn_attorney_name, s.schedule_date AS hearing_date, r.create_date, l.code_descr AS hearing_location, r.assignment_result_code AS result_code, --'NOTUSED', 'NONE' AS email_address, TRIM (p.first_name || ' ' || p.last_name) AS alj_name, schedule_time, cas.claim_id FROM adp_employee a, assignment_result r, schedule s, hearing_location h, hearing_location_code l, legal_case cas, legal_person p WHERE r.schedule_seq_num = s.schedule_seq_num AND r.legal_case_seq_num = in_num AND r.assignment_result_Seq_num = (SELECT MAX (assignment_result_seq_num) FROM assignment_result WHERE legal_case_seq_num = r.legal_case_seq_num) AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND r.legal_case_seq_num = cas.legal_case_seq_num AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND r.assignment_result_code NOT IN ('CA', 'CAC') ORDER BY 3, 5; END IF; END get_assign_results; PROCEDURE get_assign_list (recordset OUT ref_cursor, in_date IN DATE) AS BEGIN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ cas.legal_case_seq_num AS CaseId, NVL (Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num), ' ') AS case_num, cas.claim_id, a.LAST_NAME || ' ' || oregon_state_bar_number AS asgn_attorney_name, a.LAST_NAME AS attorney_name, period_case_count (atty.profile_emplid, s.schedule_date) AS attorney_case_count, period_case_load (atty.profile_emplid, s.schedule_date) AS attorney_load, TRIM (p.last_name) AS alj_name, S.KEEP_ATTORNEY_FLAG, CAS.CONSOLIDATE_CASE_FLAG, s.schedule_date AS hearing_date, s.schedule_time, l.code_descr AS hearing_location, R.ASSIGNMENT_RULE_CODE RuleCode, s.ALJ_PERSON_SEQ_NUM FROM adp_employee a, schedule s, hearing_location h, hearing_location_code l, legal_case cas, legal_person p, attorney_profile atty, assignment_result r WHERE a.emplid = s.asgn_attorney_emplid AND s.asgn_attorney_emplid = atty.profile_emplid AND TRUNC (s.assigned_date) = in_date AND s.asgn_attorney_emplid IS NOT NULL AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND s.schedule_seq_num = cas.most_recent_hearing_seq_num AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') AND S.legal_case_seq_num = R.legal_case_seq_num(+) AND s.schedule_type_code = 'HEAR' AND NVL (R.ASSIGNMENT_RESULT_CODE, 'CA') IN ('CA', 'CAC') AND S.ASSIGNED_DATE = R.CREATE_DATE(+) UNION SELECT /*+ FIRST_ROWS */ cas.legal_case_seq_num AS CaseId, Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num) AS case_num, cas.claim_id, 'Outside Counsel' AS asgn_attorney_name, 'Outside Counsel' AS attorney_name, 0 AS attorney_case_count, 0 AS attorney_load, TRIM (p.last_name) AS alj_name, S.KEEP_ATTORNEY_FLAG, CAS.CONSOLIDATE_CASE_FLAG, s.schedule_date AS hearing_date, s.schedule_time, l.code_descr AS hearing_location, 'OUTC' as RuleCode, s.ALJ_PERSON_SEQ_NUM FROM schedule s, hearing_location h, hearing_location_code l, legal_case cas, legal_person p WHERE s.asgn_attorney_emplid IS NULL AND s.asgn_other_firm_seq_num IS NOT NULL AND TRUNC (s.assigned_date) = in_date AND s.hearing_location_seq_num = h.hearing_location_seq_num AND h.hearing_location_code = l.code AND s.schedule_seq_num = cas.most_recent_hearing_seq_num AND s.schedule_type_code = 'HEAR' AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND ( s.schedule_status_code = 'SET' OR s.schedule_status_code = 'RESET') ORDER BY 4; END get_assign_list; PROCEDURE get_previously_assigned (in_num IN NUMBER, recordset OUT ref_cursor) AS BEGIN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ l.legal_case_seq_num, s2.schedule_seq_num, s2.asgn_attorney_emplid, adp.email_address, d.days_num, r.assignment_result_seq_num FROM legal_case l, schedule s, schedule s2, assignment_result r, adp_employee adp, duration_code d WHERE r.legal_case_seq_num = l.legal_case_seq_num AND r.schedule_seq_num = s.schedule_seq_num AND assignment_result_code IN ('CA', 'CAC') AND s.duration_code = d.code AND l.legal_case_seq_num = s2.legal_case_seq_num AND s2.asgn_attorney_emplid IS NOT NULL AND s2.asgn_attorney_emplid = adp.emplid AND s2.schedule_seq_num = (SELECT MAX (schedule_seq_num) FROM schedule s3 WHERE s3.legal_case_seq_num = r.legal_case_seq_num AND s3.schedule_seq_num < r.schedule_seq_num) AND r.assignment_result_seq_num > in_num; END get_previously_assigned; PROCEDURE insert_results (in_case_seq IN NUMBER, in_rule_code IN VARCHAR2, in_schedule_num IN NUMBER, in_result_code IN VARCHAR2, in_emplid IN VARCHAR2) IS BEGIN INSERT INTO assignment_result (assignment_result_seq_num, legal_case_seq_num, schedule_seq_num, assignment_rule_code, assignment_attorney_emplid, assignment_result_code, create_date) VALUES (assignment_result_seq.NEXTVAL, in_case_seq, NVL (in_schedule_num, 2), IN_rule_code, in_emplid, in_result_code, SYSDATE); END insert_results; FUNCTION max_result_seq RETURN NUMBER AS v_result_seq NUMBER; BEGIN BEGIN SELECT MAX (assignment_result_seq_num) INTO v_result_seq FROM assignment_result; EXCEPTION WHEN NO_DATA_FOUND THEN v_result_seq := 0; END; IF v_result_seq IS NULL THEN v_result_seq := 0; END IF; RETURN v_result_seq; END max_result_seq; FUNCTION rotation_conflict_check (in_emplid IN VARCHAR2, in_date IN DATE, in_hear_code IN VARCHAR2) RETURN NUMBER AS v_conflict_count NUMBER; BEGIN BEGIN --DBMS_OUTPUT.PUT_LINE('rotation ' || in_hear_loc); --dbms_output.put_line('date ' || to_char(in_date, 'mm/dd/yyyy')); --dbms_output.put_line('emplid ' || in_emplid); SELECT COUNT (*) INTO v_conflict_count FROM out_of_town_rotation_assgn WHERE attorney_emplid = in_Emplid AND TO_DATE (in_Date) BETWEEN eff_date AND exp_date AND hearing_location_code <> in_hear_code; EXCEPTION WHEN NO_DATA_FOUND THEN v_conflict_count := 0; END; RETURN v_conflict_count; END rotation_conflict_check; FUNCTION same_time_loc_check (in_date IN DATE, in_duration_code IN VARCHAR2, in_hear_loc IN NUMBER, in_alj IN NUMBER, in_is_critical IN VARCHAR2, in_request_type IN VARCHAR2, in_wrkr_conflict IN VARCHAR2) RETURN VARCHAR2 AS v_emplid VARCHAR2 (11) := 'NONE'; BEGIN IF in_is_critical = 'Y' THEN SELECT MIN (asgn_attorney_emplid) INTO v_emplid FROM schedule s, duration_code d, attorney_profile p WHERE schedule_status_code IN ('SET', 'RESET') AND in_date BETWEEN schedule_date AND schedule_date + DECODE (days_num, 0, 0, days_num - 1) AND (s.duration_code = in_Duration_code OR days_num > 0) AND alj_person_seq_num = in_alj AND hearing_location_seq_num = in_hear_Loc AND s.duration_code = d.code AND s.asgn_attorney_emplid = p.profile_emplid AND ( p.attorney_type_code = 'TRIAL' OR p.attorney_type_code = 'FED') AND p.ccu_claims_flag = 'Y' AND s.asgn_attorney_emplid IS NOT NULL; ELSE SELECT MIN (asgn_attorney_emplid) INTO v_emplid FROM schedule s, duration_code d, attorney_profile p WHERE schedule_status_code IN ('SET', 'RESET') AND in_date BETWEEN schedule_date AND schedule_date + DECODE (days_num, 0, 0, days_num - 1) AND (s.duration_code = in_Duration_code OR days_num > 0) AND alj_person_seq_num = in_alj AND hearing_location_seq_num = in_hear_Loc AND s.duration_code = d.code AND s.asgn_attorney_emplid = p.profile_emplid AND ( p.attorney_type_code = 'TRIAL' OR p.attorney_type_code = 'FED') AND ( in_wrkr_conflict = 'N' OR p.confidential_claims_flag = 'Y') AND (in_request_type = 'WCB' OR p.dcbs_claims_flag = 'Y') AND s.asgn_attorney_emplid IS NOT NULL; END IF; RETURN v_emplid; END same_time_loc_check; PROCEDURE updateSchedule (schedSeqNum IN NUMBER, attyEmplid IN VARCHAR2, asgnDate IN DATE, updateDate IN DATE, updateUser IN VARCHAR2) AS BEGIN UPDATE schedule SET asgn_attorney_emplid = attyEmplid, assigned_date = asgnDate, update_date = updateDate, update_user = updateUser WHERE schedule_seq_num = schedSeqNum; END updateSchedule; PROCEDURE calendar_review (in_loc_code IN VARCHAR2, in_date IN DATE, recordset OUT ref_cursor) AS BEGIN IF in_loc_code = 'ALL' THEN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ profile_emplid, e.LAST_NAME || ', ' || DECODE (e.preferred_name, ' ', e.FIRST_NAME, e.preferred_name) AS ATTORNEY_NAME, C.CODE_DESCR AS Location, L.LOCATION_NAME AS Description, DECODE (P.LAST_NAME, NULL, ' ', p.last_name || ', ' || P.FIRST_NAME) AS ALJ, s.schedule_time, s.schedule_status_code, Pk80001_Legalnet.get_case_num ( NVL (s.legal_case_seq_num, 0)) AS case_num, load_percent, primary_hear_loc_code, secondary_hear_loc_code, tertiary_hear_loc_code, pk80005_assignment.get_weekly_case_load (profile_emplid, in_date) AS weekly_load, pk80005_assignment.period_case_load (profile_emplid, in_date) AS case_load, pk80005_assignment.get_vacation_days (profile_emplid, in_date, 5, 0) AS vacation_days, pk80005_assignment.get_hearing_days (profile_emplid, in_date) AS hearing_days FROM adp_employee e, attorney_profile a LEFT OUTER JOIN schedule s ON a.profile_emplid = s.asgn_attorney_emplid AND NVL (S.SCHEDULE_TYPE_CODE, 'HEAR') NOT IN ('DEP', 'FDEP') AND s.schedule_date = in_date LEFT OUTER JOIN legal_person p ON S.ALJ_PERSON_SEQ_NUM = P.LEGAL_PERSON_SEQ_NUM LEFT OUTER JOIN hearing_location l ON S.HEARING_LOCATION_SEQ_NUM = L.HEARING_LOCATION_SEQ_NUM LEFT OUTER JOIN hearing_location_code c ON L.HEARING_LOCATION_CODE = C.CODE WHERE e.emplid = a.profile_emplid AND a.EXP_DATE IS NULL ORDER BY primary_hear_loc_code, weekly_load, profile_emplid; ELSE OPEN recordset FOR SELECT /*+ FIRST_ROWS */ profile_emplid, e.LAST_NAME || ', ' || DECODE (e.preferred_name, ' ', e.FIRST_NAME, e.preferred_name) AS ATTORNEY_NAME, C.CODE_DESCR AS Location, L.LOCATION_NAME AS Description, DECODE (P.LAST_NAME, NULL, ' ', p.last_name || ', ' || P.FIRST_NAME) AS ALJ, s.schedule_time, s.schedule_status_code, Pk80001_Legalnet.get_case_num ( NVL (s.legal_case_seq_num, 0)) AS case_num, load_percent, primary_hear_loc_code, secondary_hear_loc_code, tertiary_hear_loc_code, pk80005_assignment.get_weekly_case_load (profile_emplid, in_date) AS weekly_load, pk80005_assignment.period_case_load (profile_emplid, in_date) AS case_load, pk80005_assignment.get_vacation_days (profile_emplid, in_date, 5, 0) AS vacation_days, pk80005_assignment.get_hearing_days (profile_emplid, in_date) AS hearing_days FROM adp_employee e, attorney_profile a LEFT OUTER JOIN schedule s ON a.profile_emplid = s.asgn_attorney_emplid AND NVL (S.SCHEDULE_TYPE_CODE, 'HEAR') NOT IN ('DEP', 'FDEP') AND s.schedule_date = in_date LEFT OUTER JOIN legal_person p ON S.ALJ_PERSON_SEQ_NUM = P.LEGAL_PERSON_SEQ_NUM LEFT OUTER JOIN hearing_location l ON S.HEARING_LOCATION_SEQ_NUM = L.HEARING_LOCATION_SEQ_NUM LEFT OUTER JOIN hearing_location_code c ON L.HEARING_LOCATION_CODE = C.CODE WHERE e.emplid = a.profile_emplid AND a.EXP_DATE IS NULL AND ( primary_hear_loc_code = in_loc_code OR secondary_hear_loc_code = in_loc_code OR tertiary_hear_loc_code = in_loc_code) ORDER BY primary_hear_loc_code, weekly_load, profile_emplid; END IF; END calendar_review; PROCEDURE getOutlookFields (recordset OUT ref_cursor, inType IN VARCHAR2, aNum IN NUMBER ) AS BEGIN IF inType = 'CASE' THEN BEGIN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ s.schedule_seq_num, cas.claim_id, Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, schedule_status_code AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.hearing_location_code AS location_name, s.duration_code, d.days_num, adp.user_id AS atty_mailid, s.schedule_date, s.schedule_time, s.schedule_text, cas.case_weight_value, NVL (primary_case_seq_num, 0) AS consolidated FROM legal_case cas, schedule s, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, hearing_location h, consolidated_case con WHERE cas.legal_case_seq_num = aNum AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num(+) AND s.duration_code = d.code AND cas.legal_case_seq_num = primary_case_seq_num(+) AND cas.legal_case_seq_num = secondary_case_seq_num(+) AND s.schedule_date IS NOT NULL AND NVL (s.schedule_date, SYSDATE) > ADD_MONTHS (SYSDATE, -3); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; IF intype = 'ASSIGN' THEN BEGIN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ s.schedule_seq_num, cas.claim_id, Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, schedule_status_code AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.hearing_location_code AS location_name, s.duration_code, d.days_num, adp.user_id AS atty_mailid, s.schedule_date, s.schedule_time, s.schedule_text, cas.case_weight_value, NVL (primary_case_seq_num, 0) AS consolidated FROM legal_case cas, schedule s, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, hearing_location h, assignment_result r, consolidated_case con WHERE r.assignment_result_seq_num > aNum AND r.schedule_seq_num = s.schedule_seq_num AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num(+) AND s.duration_code = d.code AND cas.legal_case_seq_num = primary_case_seq_num(+) AND cas.legal_case_seq_num = secondary_case_seq_num(+) AND r.assignment_result_Seq_num = (SELECT MAX (assignment_result_seq_num) FROM assignment_result WHERE legal_case_seq_num = r.legal_case_seq_num); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; IF intype = 'VERIFY' THEN BEGIN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ s.schedule_seq_num, cas.claim_id, Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, schedule_status_code AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.hearing_location_code AS location_name, s.duration_code, d.days_num, adp.user_id AS atty_mailid, s.schedule_date, s.schedule_time, s.schedule_text, cas.case_weight_value, NVL (primary_case_seq_num, 0) AS consolidated FROM legal_case cas, schedule s, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, hearing_location h, consolidated_case con WHERE s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num(+) AND s.duration_code = d.code AND cas.legal_case_seq_num = primary_case_seq_num(+) AND cas.legal_case_seq_num = secondary_case_seq_num(+) AND ( cas.most_recent_hearing_seq_num = s.schedule_seq_num OR s.schedule_type_code <> 'HEAR') AND s.schedule_date > TRUNC (SYSDATE); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; END getOutlookFields; PROCEDURE getAttorneyPastHearings (recordset OUT ref_cursor, inUserid IN VARCHAR2) AS BEGIN IF INUserid = 'OUTCOU' THEN BEGIN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num caseId, s.schedule_seq_num scheduleId, Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num) AS caseNum, st.code_descr AS scheduleType, ss.code_descr AS scheduleStatus, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS aljname, h.city || ' - ' || h.location_name AS location, s.duration_code duration, d.days_num daysNum, firm_name AS attyName, s.schedule_date scheduleDate, s.schedule_time scheduleTime, s.assigned_date assignedDate, cas.claim_id claimId, 'OUTCOU' mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, other_firm o, hearing_location h WHERE s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num AND s.hearing_location_seq_num = h.hearing_location_seq_num AND s.duration_code = d.code AND s.schedule_seq_num = cas.most_recent_hearing_Seq_num AND o.other_firm_seq_num = s.asgn_other_firm_seq_num AND s.schedule_status_code IN ('SET', 'RESET') AND s.schedule_date < TRUNC (SYSDATE); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; ELSE BEGIN OPEN recordset FOR SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num CaseId, s.schedule_seq_num scheduleId, Pk80001_Legalnet.get_case_num (cas.legal_case_seq_num) AS caseNum, st.code_descr AS scheduleType, ss.code_descr AS scheduleStatus, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS aljName, h.city || ' - ' || h.location_name AS location, s.duration_code duration, d.days_num daysNum, adp.LAST_NAME || ', ' || DECODE (adp.preferred_name, ' ', adp.FIRST_NAME, adp.preferred_name) AS attyName, s.schedule_date AS scheduleDate, s.schedule_time AS scheduleTime, s.assigned_date AS assignedDate, cas.claim_id AS claimId, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, hearing_location h WHERE s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num AND s.duration_code = d.code AND s.schedule_seq_num = cas.most_recent_hearing_seq_num AND adp.user_id = inuserid AND s.schedule_status_code IN ('SET', 'RESET') AND s.schedule_date < TRUNC (SYSDATE) ORDER BY 10; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; END getAttorneyPastHearings; PROCEDURE getAttorneyConflictHearings (recordset OUT ref_cursor, inUserid IN VARCHAR2) AS BEGIN IF INUserid = 'CONFLICT' THEN BEGIN OPEN recordset FOR SELECT a.* FROM (SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num caseId, s.schedule_seq_num scheduleId, Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS casenum, st.code_descr AS scheduleType, ss.code_descr AS scheduleStatus, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS aljName, h.city || ' - ' || h.location_name AS location, s.duration_code duration, d.days_num daysNum, adp2.LAST_NAME || ', ' || DECODE (adp2.preferred_name, ' ', adp2.FIRST_NAME, adp2.preferred_name) AS attyName, s.schedule_date scheduleDate, s.schedule_time scheduleTime, s.assigned_date assignedDate, cas.claim_id claimId, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, adp_employee adp2, hearing_location h, attorney_profile a WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num AND s.asgn_attorney_emplid = adp.emplid -- exclude recon cases AND s.asgn_attorney_emplid = a.profile_emplid AND a.attorney_type_code <> 'APEAL' AND s.hearing_location_seq_num = h.hearing_location_seq_num AND s.duration_code = d.code AND cas.most_recent_hearing_seq_num = s.schedule_seq_num AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND d.days_num = 0 AND adp2.emplid = s.asgn_attorney_emplid AND EXISTS (SELECT 1 -- AND adp2.emplid = (SELECT MIN(asgn_attorney_emplid) FROM schedule s2, attorney_profile a2 WHERE s2.schedule_status_code IN ('SET', 'RESET') AND s.schedule_date = s2.schedule_date AND s.duration_code = s2.duration_code AND s.alj_person_seq_num = s2.alj_person_seq_num AND s.hearing_location_seq_num = s2.hearing_location_seq_num AND s2.asgn_attorney_emplid IS NOT NULL AND s2.asgn_attorney_emplid <> s.asgn_attorney_emplid -- exclude recon cases AND s2.asgn_attorney_emplid = a2.profile_emplid AND a2.attorney_type_code <> 'APEAL') UNION SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num, s.schedule_seq_num, Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, ss.code_descr AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.city || ' - ' || h.location_name AS location_name, s.duration_code, d.days_num, adp2.LAST_NAME || ', ' || DECODE (adp2.preferred_name, ' ', adp2.FIRST_NAME, adp2.preferred_name) AS atty_name, s.schedule_date, s.schedule_time, s.assigned_date, cas.claim_id claimId, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, adp_employee adp2, hearing_location h, attorney_profile a WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num AND s.asgn_attorney_emplid = adp.emplid -- exclude recon cases AND s.asgn_attorney_emplid = a.profile_emplid AND a.attorney_type_code <> 'APEAL' AND s.hearing_location_seq_num = h.hearing_location_seq_num AND s.duration_code = d.code AND cas.most_recent_hearing_seq_num = s.schedule_seq_num AND d.days_num > 0 AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND adp2.emplid = s.asgn_attorney_emplid AND EXISTS (SELECT 1 FROM schedule s2, duration_code d2, attorney_profile a2 WHERE s2.schedule_status_code IN ('SET', 'RESET') AND ( ( s.schedule_date = s2.schedule_date AND d2.days_num = 1) OR ( d2.days_num > 1 AND s.schedule_date BETWEEN s2.schedule_date AND ( s2.schedule_date + d2.days_num - 1))) AND s2.alj_person_seq_num = s.alj_person_seq_num AND s2.hearing_location_seq_num = s.hearing_location_seq_num AND s2.duration_code = d2.code AND s2.asgn_attorney_emplid IS NOT NULL AND s2.asgn_attorney_emplid <> s.asgn_attorney_emplid -- exclude recon cases AND s2.asgn_attorney_emplid = a2.profile_emplid AND a2.attorney_type_code <> 'APEAL')) a ORDER BY 11; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; ELSE BEGIN OPEN recordset FOR SELECT a.* FROM (SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num caseId, s.schedule_seq_num scheduleId, Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS caseNum, st.code_descr AS scheduleType, ss.code_descr AS scheduleStatus, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS aljName, h.city || ' - ' || h.location_name AS location, s.duration_code duration, d.days_num daysNum, adp2.LAST_NAME || ', ' || DECODE (adp2.preferred_name, ' ', adp2.FIRST_NAME, adp2.preferred_name) AS attyName, s.schedule_date scheduleDate, s.schedule_time scheduleTime, s.assigned_date assignedDate, cas.claim_id claimId, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, adp_employee adp2, hearing_location h, attorney_profile a, schedule s2, duration_code d2, attorney_profile a2 WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num AND s.asgn_attorney_emplid = adp.emplid -- exclude recon cases AND s.asgn_attorney_emplid = a.profile_emplid AND a.attorney_type_code <> 'APEAL' AND s.hearing_location_seq_num = h.hearing_location_seq_num AND s.duration_code = d.code AND cas.most_recent_hearing_seq_num = s.schedule_seq_num AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND d.days_num = 0 AND adp.user_id = inUserid AND adp2.emplid = s2.asgn_attorney_emplid AND s2.schedule_status_code IN ('SET', 'RESET') AND s.schedule_date = s2.schedule_date AND s.duration_code = s2.duration_code AND s.alj_person_seq_num = s2.alj_person_seq_num AND s2.hearing_location_seq_num = s.hearing_location_seq_num AND s2.duration_code = d2.code AND s2.asgn_attorney_emplid IS NOT NULL AND s2.asgn_attorney_emplid <> s.asgn_attorney_emplid AND s2.asgn_attorney_emplid = a2.profile_emplid AND a2.attorney_type_code <> 'APEAL' UNION SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num, s.schedule_seq_num, Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, ss.code_descr AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.city || ' - ' || h.location_name AS location_name, s.duration_code, d.days_num, adp2.LAST_NAME || ', ' || DECODE (adp2.preferred_name, ' ', adp2.FIRST_NAME, adp2.preferred_name) AS atty_name, s.schedule_date, s.schedule_time, s.assigned_date, cas.claim_id, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, adp_employee adp2, hearing_location h, attorney_profile a, schedule s2, duration_code d2, attorney_profile a2 WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num AND s.asgn_attorney_emplid = adp.emplid -- exclude recon cases AND s.asgn_attorney_emplid = a.profile_emplid AND a.attorney_type_code <> 'APEAL' AND s.hearing_location_seq_num = h.hearing_location_seq_num AND s.duration_code = d.code AND cas.most_recent_hearing_seq_num = s.schedule_seq_num AND d.days_num > 0 AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND adp.user_id = inUserid AND adp2.emplid = s2.asgn_attorney_emplid AND s2.schedule_status_code IN ('SET', 'RESET') AND ( ( s.schedule_date = s2.schedule_date AND d2.days_num = 1) OR ( d2.days_num > 1 AND s.schedule_date BETWEEN s2.schedule_date AND ( s2.schedule_date + d2.days_num - 1))) AND s2.alj_person_seq_num = s.alj_person_seq_num AND s2.hearing_location_seq_num = s.hearing_location_seq_num AND s2.duration_code = d2.code AND s2.asgn_attorney_emplid IS NOT NULL AND s2.asgn_attorney_emplid <> s.asgn_attorney_emplid AND s2.asgn_attorney_emplid = a2.profile_emplid AND a2.attorney_type_code <> 'APEAL') a ORDER BY 11; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; END getAttorneyConflictHearings; PROCEDURE getAttorneyScheduleConflict (recordset OUT ref_cursor, inUserid IN VARCHAR2) AS BEGIN OPEN recordset FOR SELECT a.* FROM (SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num caseId, s.schedule_seq_num scheduleId, wcis_dba.Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS caseNum, st.code_descr AS scheduleType, ss.code_descr AS scheduleStatus, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS aljName, h.city || ' - ' || h.location_name AS location, s.duration_code duration, d.days_num daysNum, 'Scheduled Event' AS attyName, s.schedule_date scheduleDate, s.schedule_time scheduleTime, s.assigned_date assignedDate, cas.claim_id claimId, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, hearing_location h WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num(+) AND s.duration_code = d.code AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND d.days_num = 0 AND adp.user_id = inUserid AND EXISTS (SELECT 1 FROM schedule s2 WHERE s2.schedule_status_code IN ('SET', 'RESET') AND s.schedule_date = s2.schedule_date AND s.duration_code = s2.duration_code AND ( NVL (s2.alj_person_seq_num, 0) <> NVL (s.alj_person_seq_num, 0) OR NVL ( s2.hearing_location_seq_num, 0) <> NVL ( s.hearing_location_seq_num, 0)) AND s2.asgn_attorney_emplid = s.asgn_attorney_emplid) UNION SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num, s.schedule_seq_num, wcis_dba.Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, ss.code_descr AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.city || ' - ' || h.location_name AS location_name, s.duration_code, d.days_num, 'Scheduled Event' AS atty_name, s.schedule_date, s.schedule_time, s.assigned_date, cas.claim_id, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, hearing_location h WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num(+) AND s.duration_code = d.code AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND d.days_num > 0 AND adp.user_id = inUserid AND EXISTS (SELECT 1 FROM schedule s2, duration_code d2 WHERE s2.schedule_status_code IN ('SET', 'RESET') AND ( ( s.schedule_date = s2.schedule_date AND d2.days_num = 1) OR ( d2.days_num > 1 AND s.schedule_date BETWEEN s2.schedule_date AND ( s2.schedule_date + d2.days_num - 1))) AND ( NVL (s2.alj_person_seq_num, 0) <> NVL (s.alj_person_seq_num, 0) OR NVL ( s2.hearing_location_seq_num, 0) <> NVL ( s.hearing_location_seq_num, 0)) AND s2.duration_code = d2.code AND s2.asgn_attorney_emplid = s.asgn_attorney_emplid) UNION SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num, ROWNUM AS schedule_seq_num, wcis_dba.Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, ss.code_descr AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.city || ' - ' || h.location_name AS location_name, s.duration_code, d.days_num, 'Time Off' AS atty_name, s.schedule_date, s.schedule_time, s.assigned_date, cas.claim_id, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, attorney_schedule s2, hearing_location h WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num(+) AND s.duration_code = d.code AND d.days_num = 0 AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND adp.user_id = inUserid AND s.asgn_attorney_emplid = s2.schedule_emplid AND s.schedule_date BETWEEN s2.eff_date AND s2.exp_date AND ( s.duration_code = s2.duration_code OR s2.duration_code = 'FULL') UNION SELECT /*+ FIRST_ROWS */ s.legal_case_seq_num, ROWNUM + 1000 AS schedule_seq_num, wcis_dba.Pk80001_Legalnet.get_case_num ( cas.legal_case_seq_num) AS case_num, st.code_descr AS schedule_type, ss.code_descr AS schedule_status, RTRIM (p.last_name) || ' ' || RTRIM (p.first_name) AS alj_name, h.city || ' - ' || h.location_name AS location_name, s.duration_code, d.days_num, 'Time Off' AS atty_name, s.schedule_date, s.schedule_time, s.assigned_date, cas.claim_id, adp.user_id mailId FROM legal_case cas, schedule s, schedule_status_code ss, schedule_type_code st, legal_person p, duration_code d, adp_employee adp, attorney_schedule s2, hearing_location h WHERE cas.case_status_code <> 'CLOSE' AND s.legal_case_seq_num = cas.legal_case_seq_num AND s.schedule_status_code = ss.code AND s.schedule_type_code = st.code AND s.alj_person_seq_num = p.legal_person_seq_num(+) AND s.asgn_attorney_emplid = adp.emplid AND s.hearing_location_seq_num = h.hearing_location_seq_num(+) AND s.duration_code = d.code AND d.days_num > 0 AND s.schedule_date > TRUNC (SYSDATE) AND s.schedule_status_code IN ('SET', 'RESET') AND adp.user_id = inUserid AND s.asgn_attorney_emplid = s2.schedule_emplid AND s.schedule_date BETWEEN s2.eff_date AND s2.exp_date) a ORDER BY 11; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END getAttorneyScheduleConflict; PROCEDURE getConsolFileLocation (inLegalCaseId IN NUMBER, recordset OUT ref_cursor) AS BEGIN OPEN recordset FOR SELECT c.secondary_case_seq_num, l.location_person_emplid, l.move_date, l.legal_file_location_code FROM consolidated_case c, legal_file_location l, legal_file_location l2 WHERE c.primary_case_seq_num = inLegalCaseId AND c.primary_case_seq_num = l.legal_case_seq_num AND c.secondary_case_Seq_num = l2.legal_case_seq_num AND l.legal_file_location_seq_num = (SELECT MAX (legal_file_location_seq_num) FROM legal_file_location WHERE legal_case_seq_num = c.primary_case_seq_num AND move_date = (SELECT MAX (move_date) FROM legal_file_location WHERE legal_case_seq_num = c.PRIMARY_case_seq_num)) AND l2.legal_file_location_seq_num = (SELECT MAX (legal_file_location_seq_num) FROM legal_file_location WHERE legal_case_seq_num = c.secondary_case_seq_num AND move_date = (SELECT MAX (move_date) FROM legal_file_location WHERE legal_case_seq_num = c.secondary_case_seq_num)) AND ( NVL (l.location_person_emplid, 'NONE') <> NVL (l2.location_person_emplid, 'NONE') OR NVL (l.legal_file_location_code, 'NONE') <> NVL (l2.legal_file_location_code, 'NONE')); END getConsolFileLocation; PROCEDURE Send_Assignment_Info (inText IN VARCHAR2) IS BEGIN pk10001_email.send ('gremil@saif.com', 'gremil@saif.com,timfor@saif.com,brisly@saif.com', 'Attorney Assignment Info', inText); END Send_Assignment_Info; END; ```
## Related Documentation - [Procedure catalog](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) > **Note:** Statements marked with * were inferred from code structure, naming conventions, or > contextual evidence where direct confirmation was not available in source artifacts. Statements > marked with ** could not be confirmed; treat as unknown until verified. ## Sources - Oracle DBA_SOURCE: WCIS_DBA.pk80005_assignment - Legends CLI query executed 2026-08-31: `legends oracle query --owner SAIF --name pk80005_assignment` - Calling code: `SAIF.Business.Claim.Legal` (`clsAssignmentProcess`, `clsAttorneyConflict`, `clsAttorneySchedule`)